MySQL:按子句排序会减慢搜索的匹配速度

Question

我有以下MySQL查询：

$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";

当我进行搜索时，查询使用 ORDER BY 子句加载 513.24 毫秒。 当我删除它时，它运行 77.12 毫秒。

title_ascii和description_ascii都是全文。

如何重写此查询，使运行速度加载速度比当前快得多？

解释输出：

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

Answer 1

了解引用表（pd_shares和pd_news）的架构会很有帮助。 我将子选择移动到普通连接并添加了一个分组 by 子句：

$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";