StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>I would also apply the index as suggested by Jordan.</p> <pre><code>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; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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