MySQL: ORDER BY clause slows down MATCH AGAINST search

Question

I have the following MySQL query:

$sql = "SELECT (SELECT COUNT(share_id) FROM pd_shares WHERE section = 'news' AND item_id = news.article_id) AS count_shares, article_id, link, title, publish_date, description, source FROM pd_news AS news WHERE (MATCH (title_ascii, description_ascii) AGAINST ('".match_against($_GET["sn"])."' IN BOOLEAN MODE)) AND article_id > " . $last_index . " ORDER BY article_id ASC LIMIT 0,$limit";

When I do a search, the query loads 513.24 ms with the ORDER BY clause. When I remove it, it runs 77.12 ms.

Both title_ascii and description_ascii are FULLTEXT.

How can I rewrite this Query so the run speed loads much faster than it currently is?

EXPLAIN output:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     news    fulltext    PRIMARY,news_search     news_search     0   NULL    1   Using where; Using filesort
2   DEPENDENT SUBQUERY  pd_shares   ref     section     section     19  const,my_database.news.article_id   2   Using index condition

Your EXPLAIN output told us that query should be pretty fast because it only handle few rows. Do you call EXPLAIN on actual dataset? — Zamrony P. Juhara, Mar 21 '16 at 00:31

score 0 · Answer 1 · answered Sep 30 '17 at 09:45

It would be helpful to know the schema for the referenced tables (pd_shares and pd_news). I moved the sub-select to a normal join and added a group by clause:

$sql = "SELECT
          article_id
        , link
        , title
        , publish_date
        , description
        , source 
        , COUNT(shares.share_id) AS count_shares
        FROM pd_news AS news 
        LEFT JOIN pd_shares shares
            ON shares.section = 'news' AND shares.item_id = news.article_id
        WHERE (MATCH (title_ascii, description_ascii) AGAINST ('".match_against($_GET["sn"])."' IN BOOLEAN MODE)) 
            AND article_id > " . $last_index . " 
        GROUP BY article_id
        ORDER BY article_id ASC LIMIT 0, $limit";

MySQL: ORDER BY clause slows down MATCH AGAINST search

1 Answers1