Skip navigation links
**Section Navigation** [Toggle]

MySQL 5.6 Reference Manual :: 8 Optimization :: 8.2 Optimizing SQL Statements :: 8.2.1 Optimizing SELECT Statements :: 8.2.1.4 Index Merge Optimization

- 8.2.1 Optimizing SELECT Statements
- 8.2.1.1 Speed of SELECT Statements
- 8.2.1.2 How MySQL Optimizes WHERE Clauses
- 8.2.1.3 Range Optimization
- 8.2.1.4 Index Merge Optimization
- 8.2.1.5 Engine Condition Pushdown Optimization
- 8.2.1.6 Index Condition Pushdown Optimization
- 8.2.1.7 Use of Index Extensions
- 8.2.1.8 IS NULL Optimization
- 8.2.1.9 LEFT JOIN and RIGHT JOIN Optimization
- 8.2.1.10 Nested-Loop Join Algorithms
- 8.2.1.11 Nested Join Optimization
- 8.2.1.12 Outer Join Simplification
- 8.2.1.13 Multi-Range Read Optimization
- 8.2.1.14 Block Nested-Loop and Batched Key Access Joins
- 8.2.1.15 ORDER BY Optimization
- 8.2.1.16 GROUP BY Optimization
- 8.2.1.17 DISTINCT Optimization
- 8.2.1.18 Subquery Optimization
- 8.2.1.19 Optimizing LIMIT Queries
- 8.2.1.20 How to Avoid Full Table Scans

The *Index Merge* method is used to
retrieve rows with several
`range`

scans and to merge
their results into one. The merge can produce unions,
intersections, or unions-of-intersections of its underlying
scans. This access method merges index scans from a single
table; it does not merge scans across multiple tables.

In `EXPLAIN`

output, the Index
Merge method appears as
`index_merge`

in the
`type`

column. In this case, the
`key`

column contains a list of indexes used,
and `key_len`

contains a list of the longest
key parts for those indexes.

Examples:

SELECT * FROMWHERE`tbl_name`

= 10 OR`key1`

= 20; SELECT * FROM`key2`

WHERE (`tbl_name`

= 10 OR`key1`

= 20) AND`key2`

=30; SELECT * FROM t1, t2 WHERE (t1.`non_key`

IN (1,2) OR t1.`key1`

LIKE '`key2`

%') AND t2.`value`

=t1.`key1`

; SELECT * FROM t1, t2 WHERE t1.`some_col`

=1 AND (t2.`key1`

=t1.`key1`

OR t2.`some_col`

=t1.`key2`

);`some_col2`

The Index Merge method has several access algorithms (seen in
the `Extra`

field of
`EXPLAIN`

output):

`Using intersect(...)`

`Using union(...)`

`Using sort_union(...)`

The following sections describe these methods in greater detail.

Note

The Index Merge optimization algorithm has the following known deficiencies:

If your query has a complex

`WHERE`

clause with deep`AND`

/`OR`

nesting and MySQL doesn't choose the optimal plan, try distributing terms using the following identity laws:(

AND`x`

) OR`y`

= (`z`

OR`x`

) AND (`z`

OR`y`

) (`z`

OR`x`

) AND`y`

= (`z`

AND`x`

) OR (`z`

AND`y`

)`z`

Index Merge is not applicable to full-text indexes. We plan to extend it to cover these in a future MySQL release.

Before MySQL 5.6.6, if a range scan is possible on some key, the optimizer will not consider using Index Merge Union or Index Merge Sort-Union algorithms. For example, consider this query:

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

For this query, two plans are possible:

An Index Merge scan using the

`(goodkey1 < 10 OR goodkey2 < 20)`

condition.A range scan using the

`badkey < 30`

condition.

However, the optimizer considers only the second plan.

The choice between different possible variants of the Index Merge access method and other access methods is based on cost estimates of various available options.

This access algorithm can be employed when a
`WHERE`

clause was converted to several
range conditions on different keys combined with
`AND`

, and each condition is one
of the following:

In this form, where the index has exactly

parts (that is, all index parts are covered):`N`

=`key_part1`

AND`const1`

=`key_part2`

... AND`const2`

=`key_partN`

`constN`

Any range condition over a primary key of an

`InnoDB`

table.

Examples:

SELECT * FROMWHERE`innodb_table`

< 10 AND`primary_key`

=20; SELECT * FROM`key_col1`

WHERE (`tbl_name`

=1 AND`key1_part1`

=2) AND`key1_part2`

=2;`key2`

The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.

If all columns used in the query are covered by the used
indexes, full table rows are not retrieved
(`EXPLAIN`

output contains
`Using index`

in `Extra`

field in this case). Here is an example of such a query:

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

If the used indexes don't cover all columns used in the query, full rows are retrieved only when the range conditions for all used keys are satisfied.

If one of the merged conditions is a condition over a
primary key of an `InnoDB`

table, it is not
used for row retrieval, but is used to filter out rows
retrieved using other conditions.

The applicability criteria for this algorithm are similar to
those for the Index Merge method intersection algorithm. The
algorithm can be employed when the table's
`WHERE`

clause was converted to several
range conditions on different keys combined with
`OR`

, and each condition is one
of the following:

In this form, where the index has exactly

parts (that is, all index parts are covered):`N`

=`key_part1`

AND`const1`

=`key_part2`

... AND`const2`

=`key_partN`

`constN`

Any range condition over a primary key of an

`InnoDB`

table.A condition for which the Index Merge method intersection algorithm is applicable.

Examples:

SELECT * FROM t1 WHERE=1 OR`key1`

=2 OR`key2`

=3; SELECT * FROM`key3`

WHERE (`innodb_table`

=1 AND`key1`

=2) OR (`key2`

='foo' AND`key3`

='bar') AND`key4`

=5;`key5`

This access algorithm is employed when the
`WHERE`

clause was converted to several
range conditions combined by
`OR`

, but for which the Index
Merge method union algorithm is not applicable.

Examples:

SELECT * FROMWHERE`tbl_name`

< 10 OR`key_col1`

< 20; SELECT * FROM`key_col2`

WHERE (`tbl_name`

> 10 OR`key_col1`

= 20) AND`key_col2`

=30;`nonkey_col`

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

## User Comments

Beware of having low selectivity indexes on your table. A complex AND/OR WHERE clause will surely make your query very very slow if Index_Merge optimization is being used with an intersect() algorithm.