Databases Reference
In-Depth Information
by using a strategy known as
index merge
, which permits a query to make limited use
of multiple indexes from a single table to locate desired rows. Earlier versions of MySQL
could use only a single index, so when no single index was good enough to help, MySQL
often chose a table scan. For example, the
film_actor
table has an index on
film_id
and an index on
actor_id
, but neither is a good choice for both
WHERE
conditions in this
query:
mysql>
SELECT film_id, actor_id FROM sakila.film_actor
->
WHERE actor_id = 1 OR film_id = 1;
In older MySQL versions, that query would produce a table scan unless you wrote it
as the
UNION
of two queries:
mysql>
SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1
->
UNION ALL
->
SELECT film_id, actor_id FROM sakila.film_actor WHERE film_id = 1
->
AND actor_id <> 1;
In MySQL 5.0 and newer, however, the query can use both indexes, scanning them
simultaneously and merging the results. There are three variations on the algorithm:
union for
OR
conditions, intersection for
AND
conditions, and unions of intersections for
combinations of the two. The following query uses a union of two index scans, as you
can see by examining the
Extra
column:
mysql>
EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor
->
WHERE actor_id = 1 OR film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY,idx_fk_film_id
key_len: 2,2
ref: NULL
rows: 29
Extra:
Using union(PRIMARY,idx_fk_film_id);
Using where
MySQL can use this technique on complex queries, so you might see nested operations
in the
Extra
column for some queries.
The index merge strategy sometimes works very well, but it's more common for it to
actually be an indication of a poorly indexed table:
• When the server intersects indexes (usually for
AND
conditions), it usually means
that you need a single index with all the relevant columns, not multiple indexes
that have to be combined.
• When the server unions indexes (usually for
OR
conditions), sometimes the algo-
rithm's buffering, sorting, and merging operations use lots of CPU and memory
resources. This is especially true if not all of the indexes are very selective, so the
scans return lots of rows to the merge operation.
