提高了许多LEFT一个SQL查询的性能JOIN的

Question

我有一个数据库结构与下面的协会服务了新闻报道：

• HABTM news_categories
• HABTM标签
• HABTM上传

我写了一条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秒才能执行。如果有人有任何想法，我非常感激。提前致谢。

Answer 1

由于您的“WHERE”子句最初在您的新闻类别中包含“AND”的 指定的列表，因此强制INNER加入的连接不是LEFT JOIN。 另外，我会尝试添加“STRAIGHT_JOIN”子句。这通常会强制引擎 以特别声明的顺序执行加入，而不是试图为您自己设想 替代方案......尤其是当其他表格更“查找”引用时。

我也会应用乔丹建议的指数。

SELECT STRAIGHT_JOIN 
     NA.*, 
     GROUP_CONCAT(DISTINCT tags.title) AS `tags`, 
     GROUP_CONCAT(DISTINCT tags.id) AS tag_ids, 
     GROUP_CONCAT(DISTINCT NC.title) AS news_categories, 
     GROUP_CONCAT(DISTINCT NC.id) AS news_category_ids, 
     GROUP_CONCAT(DISTINCT NC.slug) AS news_category_slugs, 
     NAUp.`caption` AS upload_caption, 
     Up1.`title` AS upload_title, 
     Up1.`basename` AS upload_basename, 
     Up1.`extension` AS upload_extension, 
     Up1.`path` AS upload_path 
    FROM 
     news_articles NA 
     INNER JOIN news_articles_news_categories NACats 
      ON NA.id = NACats.news_article_id 

      INNER JOIN news_categories NC 
       ON NACats.news_category_id = NC.id 
       AND NC.slug IN ("category-one", 
           "category-two", 
           "category-three", 
           "category-four", 
           "category-five") 


     LEFT JOIN news_articles_tags NATags 
      ON NA.ID = NATags.news_article_id 

      LEFT JOIN tags 
       ON NATags.tag_id = tags.id 

     LEFT JOIN news_articles_uploads NAUp 
      ON NA.ID = NAUp.news_article_id 
       AND NAUp.order = 0 

      LEFT JOIN uploads Up1 
       ON NAUp.upload_id = Up1.id 

    WHERE 
     NA.Published = 1 
    GROUP BY 
     NA.ID 
    ORDER BY 
     NA.lead_article DESC, 
     NA.created DESC 
    LIMIT 20; 

Answer 2

你有一个索引news_articles。 lead_article，news_articles。 created，这是一个包含两列的索引。

create index news_articles_x1 on news_articles (lead_articles, created); 

没有它，你将不会利用limit子句的顺序，它仍然会扫描整个表并进行排序。

另外我会问你是否需要在一次爆炸中的所有这些数据？