5

How I can increase speed of this search query?

This search bring news based on time and title and text. each item has own factor.

SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` , 
(

    (

       CASE
          WHEN time >=1432173380 THEN 21
          WHEN time >=1432162580 THEN 18
          WHEN time >=1432097780 THEN 15
          WHEN time >=1431903380 THEN 11
          WHEN time >=1431320180 THEN 7
          ELSE 0
       END
    ) * 1.75 ) AS `timescore` ,

    MATCH (`text`) AGAINST ('tax in work') * 0.65 AS `textscore`

    FROM `news`
    HAVING `titlescore` + `timescore` + `textscore` >= 35
    ORDER BY `titlescore` + `timescore` + `textscore` DESC
    LIMIT 0 , 25

Now this takes (4 to 20)sec relay on search query!

Explain query result:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE         news     ALL     NULL            NULL    NULL       NULL 1656038     Using where; Using filesort

Table structure:

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` text COLLATE utf8_persian_ci NOT NULL,
  `text` varchar(400) COLLATE utf8_persian_ci DEFAULT NULL,
  `time` int(10) unsigned NOT NULL,
  `cat_id` int(10) unsigned NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `visit` int(10) unsigned NOT NULL,
  `url` text COLLATE utf8_persian_ci NOT NULL,
  `sha1` varchar(40) COLLATE utf8_persian_ci NOT NULL,
  `title_slug` text COLLATE utf8_persian_ci NOT NULL,
  `date` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
  `day_time` tinyint(4) DEFAULT NULL,
  `week_day` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sha1Uniq` (`sha1`),
  KEY `cat_id` (`cat_id`),
  KEY `source_id` (`source_id`),
  KEY `time` (`time`),
  KEY `date` (`date`),
  FULLTEXT KEY `searchIndex` (`title`,`text`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=2386224 ;
  • @EmRa228 unfortunately you have columns of text type in results, that forbids using in-memory temp table so all those records have to be copied to disk - you might try to actually first create temporary result with only needed numerical columns (result of match is numeric afaik, not text), so the union will send much smaller data to memory table only, and then join resulting 25 rows to full data by primary key so you get them fast. – jkavalik May 23 '15 at 20:50
  • Could you upload some data to the web somewhere - it might help to have something concrete - you can md5 anything sensitive. – Vérace May 23 '15 at 20:59
3
+50

First retrieve id and the computed scores, then sort and join the result back to news:

SET @line = 0;
SET @last = 25;
SELECT B.*,titlescore,timescore,textscore FROM
(
    SELECT id,titlescore,timescore,textscore,combinedscore,(@line:=@line+1) line
    FROM
    (
        SELECT id,
            MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore`
            (
                CASE
                    WHEN time >=1432173380 THEN 21
                    WHEN time >=1432162580 THEN 18
                    WHEN time >=1432097780 THEN 15
                    WHEN time >=1431903380 THEN 11
                    WHEN time >=1431320180 THEN 7
                    ELSE 0
                END
            ) * 1.75 ) AS `timescore`,
            MATCH (`text`) AGAINST ('tax in work') * 0.65 AS `textscore`,
            MATCH (`title`) AGAINST ('tax in work') * 1.65+
            (
                CASE
                    WHEN time >=1432173380 THEN 21
                    WHEN time >=1432162580 THEN 18
                    WHEN time >=1432097780 THEN 15
                    WHEN time >=1431903380 THEN 11
                    WHEN time >=1431320180 THEN 7
                    ELSE 0
                END
            ) * 1.75 ) + MATCH (`text`) AGAINST ('tax in work') * 0.65
            AS combinedscore
        FROM news
    ) AA WHERE combinedscore >= 35
    ORDER BY combinedscore DESC
) A LEFT JOIN B news USING (id)
WHERE line <= @last;

Give it a Try !!!

| improve this answer | |
0
SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` , 
    (
      ( CASE
          WHEN time >=1432173380 THEN 21
          WHEN time >=1432162580 THEN 18
          WHEN time >=1432097780 THEN 15
          WHEN time >=1431903380 THEN 11
          WHEN time >=1431320180 THEN 7
          ELSE 0
       END
      ) * 1.75
    ) AS `timescore` ,

    MATCH (`text`) AGAINST ('tax in work') * 0.65 AS `textscore`

    FROM `news`

    WHERE MATCH(title, `text`) AGAINST ('tax in work')  -- Added this

    HAVING   `titlescore` + `timescore` + `textscore` >= 35
    ORDER BY `titlescore` + `timescore` + `textscore` DESC
    LIMIT 0 , 25;

All I did was to add the WHERE clause. I expect this to significantly cut down on the number of rows actually looked at.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.