 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 three solutions for various sizes of tables.
Figure 21-3. IN, EXISTS, and JOIN solution scalability (indexed query)
However, when no index exists to support the subquery or the join, then the join will outperform both IN and EXISTS subqueries. It will also degrade less rapidly as the number of rows to be processed increases. This is because of the MySQL join optimizations. Figure 21-4 shows the performance characteristics of the three solutions where no index exists.
Figure 21-4. Comparison of nonindexed JOIN, IN, and EXISTS performance
21.1.3. Using Subqueries in Complex Joins
Although a subquery, in general, will not outperform an equivalent join, there are occasions when you can use subqueries to obtain more favorable execution plans for complex joins especially when index merge operations are concerned.
Let's look at an example. You have an application that from time to time is asked to report on the quantity of sales made to a particular customer by a particular sales rep. The SQL might look like Example 21-4.
Example 21-4. Complex join SQL
SELECT COUNT(*), SUM(sales.quantity), SUM(sales.sale_value) FROM sales JOIN customers ON (sales.customer_id=customers.customer_id) JOIN employees ON (sales.sales_rep_id=employees.employee_id) JOIN products ON (sales.product_id=products.product_id) WHERE customers.customer_name='INVITRO INTERNATIONAL' AND employees.surname='GRIGSBY' AND employees.firstname='RAY' AND products.product_description='SLX';
We already have an index on the primary key columns for customers, employees, and products, so MySQL uses these indexes to join the appropriate rows from these tables to the sales table. In the process, it eliminates all of the rows except those that match the WHERE clause condition:
Short Explain ------------- 1 SIMPLE select(ALL) on sales using no key 1 SIMPLE select(eq_ref) on employees using PRIMARY Using where 1 SIMPLE select(eq_ref) on customers using PRIMARY Using where 1 SIMPLE select(eq_ref) on products using PRIMARY Using where
This turns out to be a fairly expensiv