A subquery is a SQL statement that is embedded within the WHERE clause of another statement. For instance, Example 21-1 uses a subquery to determine the number of customers who are also employees.
Example 21-1. SELECT statement with a subquery
SELECT COUNT(*) FROM customers WHERE (contact_surname, contact_firstname,date_of_birth) IN (select surname,firstname,date_of_birth FROM employees)
We can identify the subquery through the DEPENDENT SUBQUERY tag in the Select type column of the EXPLAIN statement output, as shown here:
Explain plan ------------ ID=1 Table=customers Select type=PRIMARY Access type=ALL Rows=100459 Key= (Possible= ) Ref= Extra=Using where ID=2 Table=employees Select type=DEPENDENT SUBQUERY Access type=ALL Rows=1889 Key= (Possible= ) Ref= Extra=Using where
The same query can also be rewritten as an EXISTS subquery, as in Example 21-2.
Example 21-2. SELECT statement with an EXISTS subquery
SELECT count(*) FROM customers WHERE EXISTS (SELECT 'anything' FROM employees where surname=customers.contact_surname AND firstname=customers.contact_firstname AND date_of_birth=customers.date_of_birth) Short Explain ------------- 1 PRIMARY select(ALL) on customers using no key Using where 2 DEPENDENT SUBQUERY select(ALL) on employees using no key
Note that the EXPLAIN output for the EXISTS subquery is identical to that of the IN subquery. This is because MySQL rewrites IN-based subqueries as EXISTS-based syntax before execution. The performance of subqueries will, therefore, be the same, regardless of whether you use the EXISTS or the IN operator.
21.1.1. Optimizing Subqueries
When MySQL executes a statement that contains a subquery in the WHERE clause, it will execute the subquery once for every row returned by the main or "outer" SQL statement. It therefore follows that the subquery had better execute very efficiently: it is potentially going to be executed many times. The most obvious way to make a subquery run fast is to ensure that it is supported by an index. Ideally, we should create a concatenated index that includes every column referenced within the subquery.
For our example query in the previous example, we should create an index on all the employees columns referenced in the subquery:
CREATE INDEX i_customers_name ON customers (contact_surname, contact_firstname, date_of_birth)
We can see from the following EXPLAIN output that MySQL makes use of the index to resolve the subquery. The output also includes the Using index clause, indicating that only the index is usedthe most desirable execution plan for a subquery.
Short Explain ------------- 1 PRIMARY select(ALL) on employees using no key Using where 2 DEPENDENT SUBQUERY select(index_subquery) on customers using i_customers_name Using index; Using where
Figure 21-1 shows the relative performance of both the EXISTS and IN subqueries with and without an index.
Figure 21-1. Subquery performance with and without an index
Not only will an indexed subquery outperform a nonindexed subquery, but the un-indexed subquery will also degrade exponentially as the number of rows in each of the tables increases. (The response time will actually be proportional to the number of rows returned by the outer query times the number of rows accessed in the subquery.) Figure 21-2 shows this exponential degradation.
21.1.2. Rewriting a Subquery as a Join
Many subqueries can be rewritten as joins. For instance, our example subquery could have been expressed as a join, as shown in Example 21-3.
Figure 21-2. Exponential degradation in nonindexed subqueries
Example 21-3. Subquery rewritten as a join
SELECT count(*) FROM customers JOIN employees ON (employees.surname=customers.contact_surname AND employees.firstname=customers.contact_firstname AND employees.date_of_birth=customers.date_of_birth)
Subqueries sometimes result in queries that are easier to understand, and when the subquery is indexed, the performance of both types of subqueries and the join is virtually identical, although, as described in the previous section, EXISTS has a small advantage over IN. Figure 21-3 compares the 