mysql - 提高 MySQL 查询性能

Question

我有以下查询，它连接了 5 个 InnoDB 相关表以获得所需的 10 行结果集，我尽力通过添加索引并以多种不同方式重写查询来解决问题，但我最终要么意外的结果或非常慢的查询。

这里是查询

SELECT 
    a.*,
    c.id as category_id,
    c.title as catname,
    CONCAT(u.fname, ' ', u.lname) as username,
    DATE_FORMAT(a.created, '%W %M %d, %Y - %T') as long_date,
    DATE_FORMAT(a.created, '%d/%m/%Y - %T') as short_date,
    (SELECT 
            COUNT(article_id)
        FROM
            comment
        WHERE 
            article_id = a.id) as totalcomments,
    YEAR(a.created) as year,
    MONTH(a.created) as month,
    DAY(a.created) as day
FROM
    article as a
        INNER JOIN
    article_related_categories rc ON a.id = rc.article_id
        LEFT JOIN
    category as c ON c.id = rc.category_id
        LEFT JOIN
    user as u ON u.id = a.user_id
WHERE
    rc.category_id = 1
        AND a.created <= NOW()
        AND (a.expire = '0000-00-00 00:00:00'
        OR a.expire >= NOW())
        AND a.published IS NOT NULL

ORDER BY a.created DESC
LIMIT 0 , 10

Click Here to see the explain screenshot

目前文章表有13000多行，预计会快速增长。

麻烦的是，执行此查询可能需要很长时间，大约需要 3-4 秒。我怀疑 INNER JION 导致了大部分问题，但我想我会在这里询问是否有人对提高此查询的性能有任何想法。

Answer 1

嵌套 SELECT 可能会减慢速度。加入 comment 表和 GROUP BY a.id:

...
    COUNT(*) as totalcomments,
...    
FROM
    ...
    LEFT JOIN comment AS comm.article_id = a.id
WHERE
    ...
GROUP BY a.id