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 expensive query, because we have to perform a full scan of the large sales table. What we probably want to do is to retrieve the appropriate primary keys from products, customers, and employees using the WHERE clause conditions, and then look up those keys (quickly) in the sales table. To allow us to quickly find these primary keys, we would create the following indexes:
CREATE INDEX i_customer_name ON customers(customer_name); CREATE INDEX i_product_description ON products(product_description); CREATE INDEX i_employee_name ON employees(surname, firstname);
To enable a rapid sales table lookup, we would create the following index:
CREATE INDEX i_sales_cust_prod_rep ON sales(customer_id,product_id,sales_rep_id);
Once we do this, our execution plan looks like this:
Short Explain ------------- 1 SIMPLE select(ref) on customers using i_customer_name Using where; Using index 1 SIMPLE select(ref) on employees using i_employee_name Using where; Using index 1 SIMPLE select(ref) on products using i_product_description Using where; Using index 1 SIMPLE select(ref) on sales using i_sales_cust_prod_rep Using where
Each step is now based on an index lookup, and the sales lookup is optimized through a fast concatenated index. The execution time reduces from about 25 seconds (almost half a minute) to about 0.01 second (almost instantaneous).
As we noted in the previous chapter, we can't always create all of the concatenated indexes that we might need to support all possible queries on a table. In this case, we may want to perform an "index merge" of multiple single-column indexes. However, MySQL will not normally perform an index merge when optimizing a join.
In this case, to get an index merge join, we can try to rewrite the join using subqueries, as shown in Example 21-5.
Example 21-5. Complex join SQL rewritten to support index merge
SELECT COUNT(*), SUM(sales.quantity), SUM(sales.sale_value) FROM sales WHERE product_id= (SELECT product_id FROM products WHERE product_description='SLX') AND sales_rep_id=(SELECT employee_id FROM emplo