我有一个数据库结构,用于提供新闻文章,以下是相关联的内容:
- 多对多关系 news_categories
- 多对多关系 tags
- 多对多关系 uploads
我编写了一条SQL查询语句来汇总所有内容:
SELECT `news_articles`.*,
GROUP_CONCAT(DISTINCT tags.title) AS `tags`,
GROUP_CONCAT(DISTINCT tags.id) AS `tag_ids`,
GROUP_CONCAT(DISTINCT news_categories.title) AS `news_categories`,
GROUP_CONCAT(DISTINCT news_categories.id) AS `news_category_ids`,
GROUP_CONCAT(DISTINCT news_categories.slug) AS `news_category_slugs`,
`news_articles_uploads`.`caption` AS `upload_caption`,
`uploads`.`title` AS `upload_title`,
`uploads`.`basename` AS `upload_basename`,
`uploads`.`extension` AS `upload_extension`,
`uploads`.`path` AS `upload_path`
FROM `news_articles`
LEFT JOIN `news_articles_tags` ON news_articles_tags.news_article_id = news_articles.id
LEFT JOIN `tags` ON news_articles_tags.tag_id = tags.id
LEFT JOIN `news_articles_news_categories` ON news_articles_news_categories.news_article_id = news_articles.id
LEFT JOIN `news_categories` ON news_articles_news_categories.news_category_id = news_categories.id
LEFT JOIN `news_articles_uploads` ON (news_articles_uploads.news_article_id = news_articles.id AND news_articles_uploads.order = 0)
LEFT JOIN `uploads` ON news_articles_uploads.upload_id = uploads.id
WHERE (news_categories.slug IN ("category-one","category-two","category-three","category-four","category-five")) AND (news_articles.published = 1)
GROUP BY `news_articles`.`id`
ORDER BY `news_articles`.`lead_article` DESC, `news_articles`.`created` DESC LIMIT 20;
问题在于查询运行速度缓慢,在繁忙时期,CPU使用率变得相当失控!以下是上述查询的解释(右键单击在新选项卡中查看完整大小):您可以在此处找到模式:http://pastie.org/private/qoe2qo16rbqr5mptb4bug服务器运行MySQL 5.1.55,网站使用Zend Framework执行查询和PHP 5.2.8。我已经检查了MySQL慢查询日志,并尽我所知添加了缺少的索引,但是该查询仍然需要1-3秒钟才能执行。如果有人有任何想法,我将非常感激。提前致谢。
news_articles.id
。 - Ben