e 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 employees WHERE surname='GRIGSBY' AND firstname='RAY') AND customer_id= (SELECT customer_id FROM customers WHERE customer_name='INVITRO INTERNATIONAL');
The EXPLAIN output shows that an index merge will now occur, as shown in Example 21-6.
Example 21-6. EXPLAIN output for an index merge SQL
Short Explain ------------- 1 PRIMARY select(index_merge) on sales using i_sales_rep,i_sales_cust Using intersect(i_sales_rep,i_sales_cust); Using where 4 SUBQUERY select(ref) on customers using i_customer_name 3 SUBQUERY select(ref) on employees using i_employee_name 2 SUBQUERY select(ref) on products using i_product_description
The performance of the index merge solution is about 0.025 secondslower than the concatenated index but still about 1,000 times faster than the initial join performance. This is an especially useful technique if you have a STAR schema (one very large table that contains the "facts," with foreign keys pointing to other, smaller "dimension" tables).
Figure 21-5 compares the performance of the three approaches. Although an index merge is not quite as efficient as a concatenated index, you can often satisfy a wider range of queries using an index merge, since this way you need only create indexes on each column, not concatenated indexes on every possible combination of columns.
Figure 21-5. Optimizing a complex join with subqueries and index merge
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Program