MySQL Optimizer Features

MySQL can use an index for a WHERE, ORDER BY, or GROUP BY column; however, generally MySQL will select only one index per table. Starting with MySQL 5.0, there are a small number of exceptions when the optimizer may utilize more than one index, but in earlier versions this could result in a slower query. The most commonly seen index merge is a union between two indexes, generally found when you perform an OR operation on two high cardinality indexes. Here is an example:

mysql> SET @@session.optimizer_switch='index_merge_intersection=on';
mysql> EXPLAIN SELECT artist_id, name
    -> FROM  artist
    -> WHERE name = 'Queen'
    -> OR    founded = 1942\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: artist
         type: index_merge
possible_keys: name,founded,founded_2
          key: name,founded
      key_len: 257,2
          ref: NULL
         rows: 500
        Extra: Using union(name,founded); Using where

NOTE 

The optimizer_switch system variable that could enable or disable these additional options was first introduced in MySQL 5.1. For more information see http://dev.mysql.com/doc/refman/5.1/en/switchable-optimizations.html.

A second type of index merge is the intersection of two less unique indexes, as shown here:

mysql> SET @@session.optimizer_switch='index_merge_intersection=on';
mysql> EXPLAIN SELECT artist_id, name
    -> FROM  artist
    -> WHERE type = 'Band'
    -> AND   founded = 1942\G
...
       Extra: Using intersect(founded,type); Using where

The third type of index merge is similar to a union between two indexes; however, one index must first be sorted:

mysql> EXPLAIN SELECT artist_id, name
    -> FROM artist
    -> WHERE name = 'Queen'
    -> OR (founded BETWEEN 1942 AND 1950)\G
...
       Extra: Using sort_union(name,founded); Using where

You can find more information about these index merges at http://dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html.

As part of creating these examples, the author discovered a new case never seen before with any client queries. The following is a three index merge example:

mysql> EXPLAIN SELECT artist_id, name
    -> FROM artist
    -> WHERE name = 'Queen'
    -> OR (type = 'Band' AND founded = '1942')\G
...
       Extra: Using union(name,intersect(founded,type)); Using where

TIP

You should always evaluate if a multi column index is more efficient than letting the optimizer merge indexes.

The advantage of having multiple single column indexes or multiple multi column indexes can only be determined based on the types of queries and query volumes for your specific application. Several single column indexes of high cardinality columns combined with index merge capabilities may provide greater flexibility for very different query conditions. The performance considerations of writes may also affect the most optimal data access paths for retrieving data.