MySQL Subqueries

November 15, 2004

MYSQL 4.1 has been
released as a production version, and with all the new features it is likely a
new generation of developers will soon be using MySQL. This month I revisit an
old area of contention - subqueries. Until now, MySQL has not supported
subqueries, and this lack caused many to write off MySQL as not being a serious
DBMS. While the lack was certainly a problem, many developers do not know that
subqueries can often be rewritten as a join, sometimes giving a performance
benefit in the process. This month I look at subqueries, and how they can be
rewritten in a more optimal way.

Which query is better?
Some may argue that the former query is more readable, but I think that to
anyone vaguely competent in SQL they should both be clear. However, one is
definitely more efficient than the other is. Using EXPLAIN, let's see which.
First, the join:

This time MySQL has to
examine 16 rows, a marginal difference in such a small example, but this
difference can add up in larger datasets.

Rewriting subqueries as Outer Joins

A common kind of query is
finding all records that do not have an associated record in another table--in
this case, finding all animals that do not have an associated food. Here is how
you would do it with a subquery:

I can hear some sighs of
relief - the subquery in this case is as efficient as the outer join. Outer
joins can often deliver performance gains though, so benchmark your queries.
Note that an alternative way to write the same subquery is as follows (this is
as efficient in this case)