how to increase speed of this mysql query?

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=utf

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

StackExchange Database Administrators Q#102118, answer score: 3