Databases
StackOverflow2013
Postsdbo
POImproving the performance of an SQL query with many LEFT JOINs
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POImproving the performance of an SQL query with many LEFT JOINs
    primarykey
    data
    text
    <p>I have a database structure serving up news articles with the following associations:</p> <ul> <li>HABTM news_categories</li> <li>HABTM tags</li> <li>HABTM uploads</li> </ul> <p>I have written an SQL query to pull all this together:</p> <pre><code> 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; </code></pre> <p>The problem is that whilst the query runs, it is slow, and during busy periods the CPU usage is getting pretty out of hand!</p> <p>Here is an EXPLAIN for the above query (right-click open in new tab to see full size):</p> <p><img src="https://i.stack.imgur.com/SVJPS.png" alt="Explain result for the above query"></p> <p>You can find the schema here: <a href="http://pastie.org/private/qoe2qo16rbqr5mptb4bug" rel="nofollow noreferrer">http://pastie.org/private/qoe2qo16rbqr5mptb4bug</a></p> <p>The server is running MySQL 5.1.55 and the website uses Zend Framework to execute the query and PHP 5.2.8.</p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBeesonBison
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POImproving the performance of an SQL query with many LEFT JOINs
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POImproving the performance of an SQL query with many LEFT JOINs
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload