yees 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 Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development