Optimising MySQL queries – JOIN vs IN (subquery)

When searching for some advice relating to an SQL query I was writing recently, I read some advice that you should try to use JOINs rather than IN with a subquery. I modified the particular query I was working on to use a JOIN instead of a subquery, and lo and behold, it was much faster.

So I decided to try and rewrite some other queries I’d written using IN with a subquery. Re-written to use JOINs instead, they should be much faster. But rather than just blindly rewriting the queries, I decided to run a few tests.

I should probably note here that it is certainly possible that I messed up in writing the tests, or in calculating the results. Just like everyone else, I sometimes make mistakes. If anyone thinks I have made a mistake somewhere, please leave a comment to let me know what I did wrong. You can see the test and the results below.

What I found is that actually whether a JOIN or IN subquery is faster depends on how much of the data the query selects. For queries that only returned one row, a JOIN was a lot faster. For queries that returned a lot of rows, a subquery was faster. It seems to relate to how many rows exist in the table being queried, and how many records your subquery returns. This is partly due to how MySQL evaluates queries – the outer query is evaluated first, and then the subquery.

To give some number examples, in my test using IN subquery took around 0.0264470117s on average when the subquery returned only one row, but took 0.013940505s (about half the time) when the subquery matched 1215 rows. (The table results were returned from held 3791 records in total). Using JOIN took around 0.0007992231s when there was only one matching row, extremely fast compared to using IN subquery. But when the query matched 1215 rows, it was quite a bit slower than using a subquery, taking 0.0369988716s on average. These were all with LIMIT 0,30.

On a more complicated query, using a subquery took 0.0057s when matching 1215 rows, and 0.0307s for 1 row. Using JOINs instead took 1.1343s when matching 1215 rows, and 0.0041 for 1 row. Over 1s vs 0.0057s is a massive difference in favour of using subqueries.

INNER JOIN vs LEFT JOIN where no rows will be NULL

Another thing I learned recently was that when you are JOINing with a table where you are using a JOIN ON or WHERE clause that means no rows will be NULL, then you should (or at least might as well) use INNER JOIN rather than LEFT JOIN. I tested this for efficiency, and it doesn’t seem to make any difference.

DISTINCT vs GROUP BY

Finally, I decided to test DISTINCT against GROUP BY, both methods that can be used to get results with no duplicate records. Now, according to most of the people responding to this question: What’s faster, SELECT DISTINCT or GROUP BY in MySQL? they are both equivalent, or DISTINCT may be faster depending on whether you have an INDEX on the column being sorted.

Notice, however, that apart from one poster, all the replies are based on theory rather than actual test results. The one person who does supply a test result shows GROUP BY to be faster.

In my tests, I found GROUP BY was always faster than DISTINCT, though by such as small amount as to not matter for most queries. When using a JOIN rather than a subquery though, GROUP BY was about twice as fast as using DISTINCT.

Conclusion

For the particular queries that these tests were based on, I intend on keeping them as subqueries. Generally the pages viewed most will be those where a lot of records are matched. Although it is slower when matching fewer records, the speed is still perfectly fine. The slowest query when using a subquery was faster than the slowest query when using a JOIN.

I will change my queries to use INNER JOIN where applicable, just because it fits in with the logic of the query rather than any benefit. I will also continue to use GROUP BY rather than DISTINCT.

Ultimately, the takeaway is that you shouldn’t just trust information you read. You should try it out with your own queries and see whether it does provide any benefit or not. And remember to test with different parameters to see how well it copes with selecting lots or only a few records.

Testing Methodology

When testing different ways of doing things, I like to use a loop to repeat the same actions multiple times. This lets you get a better picture of the average speed. I also like to call the tests in a random order. This way, if there is something that makes the last test run slower, it will be equalled out across all tests, as they all have an equal chance of being run last. It also means that things like how much CPU time your computer is using for doing other stuff should affect all tests equally.

Below is the test that I ran. globalFuncs.inc.php is a file that sets up the database connection and assigns it to $conn. This test won’t be any good for you to run on your own server, since you don’t have my database. But it should give you an idea of what I tested, and how my testing methodology works.

The above generated the following output, which lists every different query tested, along with the number of tests of that query and the average speed:

You can download the results spreadsheet, which also has some calculations I did to summarize the results here: results.ods.gz (Gzipped openoffice spreadsheet). The summary results are below. Avg refers to the average time the query took, Num refers to the number of times that type of query was run. (The higher the number, the more reliable the average figure).

INNER JOIN vs LEFT JOIN

Type	Avg	Num
subquery	0.0258382885	1256
subquery inner	0.0250546769	1227
subquery as table	0.1610412084	1276
subquery as table inner	0.152149437	1277
JOIN	0.020252807	1225
JOIN inner	0.0205932834	1255
subquery EXISTS	0.0251283091	1208
subquery EXISTS inner	0.0247927192	1276

Lots of records (id 16) vs small amount (id 22) w/ LIMIT 0,30 & LEFT JOIN / INNER aggregated

Type	id	Avg	Num
subquery	22	0.0264470117	643
subquery	16	0.013940505	634
subquery as table	22	0.0047066287	692
subquery as table	16	0.2215603	590
JOIN	22	0.0007992231	590
JOIN	16	0.0369988716	633
subquery EXISTS	22	0.0260955988	644
subquery EXISTS	16	0.0144021435	593

GROUP BY vs DISTINCT w/ LEFT JOIN / INNER aggregated

Type	Group	Avg	Num
subquery	group by	0.0253810094	1200
subquery	distinct	0.0255165765	1283
subquery as table	group by	0.1561119418	1310
subquery as table	distinct	0.1571011819	1243
JOIN	group by	0.0136067026	1202
JOIN	distinct	0.0268380302	1278
subquery EXISTS	group by	0.024847748	1245
subquery EXISTS	distinct	0.0250646173	1239

ORDER w/ type=JOIN / JOIN inner & LIMIT 0,30

Group	Order	Avg	Num
group by	images.id	0.0122829478	278
distinct	images.id	0.0278775495	315
group by	images.Rating, images.id	0.0122543925	315
distinct	images.Rating, images.id	0.02487494	315