Mysql – how to increase speed of this thesql query

index-tuningMySQLperformancequery-performance

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 ;

Best Answer

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 !!!

Related Question