I have a database structure serving up news articles with the following associations:
I have written an SQL query to pull all this together:
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`
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;
The problem is that whilst the query runs, it is slow, and during busy periods the CPU usage is getting pretty out of hand!
Here is an EXPLAIN for the above query (right-click open in new tab to see full size):
You can find the schema here: http://pastie.org/private/qoe2qo16rbqr5mptb4bug
The server is running MySQL 5.1.55 and the website uses Zend Framework to execute the query and PHP 5.2.8.
I've been through the MySQL slow query log and added missing indexes to the best of my knowledge but the query still shows up as taking 1-3 seconds to execute. If anyone has any ideas I've be very grateful. Thanks in advance.
Since your "WHERE" clause originally included "AND" for your news categories within a specified list, that would FORCE the joins to get there as INNER joins, not LEFT JOINs. Also, I would try adding the "STRAIGHT_JOIN" clause. This typically forces the engine to do the joining in the order specifically stated, instead of trying to think of its own alternative for you... especially when the other tables are more "lookup" references.
I would also apply the index as suggested by Jordan.
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;
Do you have an index on
created, an index that has both columns.
create index news_articles_x1 on news_articles (lead_articles, created);
Without it, you won't be taking advantage of the order by limit clause, it would still scan and sort the whole table.
Also I would question whether you need all of this data in one blast?