Databases
StackOverflow2013
Postsdbo
POwhy is MySQL JOIN significantly faster than WHERE IN (subquery)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POwhy is MySQL JOIN significantly faster than WHERE IN (subquery)
    text
    copied!<p>I am trying to better understand why this query optimization is so significant (over 100 times faster) so I can reuse similar logic for other queries.</p> <p>Using MySQL 4.1 - RESET QUERY CACHE and FLUSH TABLES was done before all queries and result time can be reproduced consistently. Only thing that is obvious to me on the EXPLAIN is that only 5 rows have to be found during the JOIN ? But is that the whole answer to the speed? Both queries are using a partial index (forum_stickies) to determine deleted topics status (topic_status=0)</p> <p>Screenshots for deeper analysis with EXPLAIN</p> <ul> <li><a href="http://img195.imageshack.us/img195/9494/mysqlfaster.png" rel="nofollow noreferrer">http://img195.imageshack.us/img195/9494/mysqlfaster.png</a></li> </ul> <p>slow query: 0.7+ seconds (cache cleared)</p> <pre><code>SELECT SQL_NO_CACHE forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN (SELECT SQL_NO_CACHE MAX (topic_last_post_id) AS topic_last_post_id FROM bb_topics WHERE topic_status=0 GROUP BY forum_id) </code></pre> <p>fast query: 0.004 seconds or less (cache cleared)</p> <pre><code>SELECT SQL_NO_CACHE forum_id, topic_id FROM bb_topics AS s1 JOIN (SELECT SQL_NO_CACHE MAX(topic_last_post_id) AS topic_last_post_id FROM bb_topics WHERE topic_status=0 GROUP BY forum_id) AS s2 ON s1.topic_last_post_id=s2.topic_last_post_id </code></pre> <p>Note there is no index on the most important column (<code>topic_last_post_id</code>) but that cannot be helped (results are stored for repeated use anyway).</p> <p>Is the answer simply because the first query has to scan <code>topic_last_post_id</code> TWICE, the second time to match up the results to the subquery? If so, why is it exponentially slower? </p> <p>(less important I am curious why the first query still takes so long if I actually do put an index on <code>topic_last_post_id</code>)</p> <p>update: I found this thread on stackoverflow after much searching later on which goes into this topic <a href="https://stackoverflow.com/questions/141278/subqueries-vs-joins">Subqueries vs joins</a></p>
 

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