### 7.13.2. Index Merge Optimization

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 * FROM `tbl_name` WHERE `key1` = 10 OR `key2` = 20;

SELECT * FROM `tbl_name`
WHERE (`key1` = 10 OR `key2` = 20) AND `non_key`=30;

SELECT * FROM t1, t2
WHERE (t1.`key1` IN (1,2) OR t1.`key2` LIKE '`value`%')
AND t2.`key1`=t1.`some_col`;

SELECT * FROM t1, t2
WHERE t1.`key1`=1
AND (t2.`key1`=t1.`some_col` OR t2.`key2`=t1.`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 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.

• 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:

```(`x` AND `y`) OR `z` = (`x` OR `z`) AND (`y` OR `z`)
(`x` OR `y`) AND `z` = (`x` AND `z`) OR (`y` AND `z`)
```
• Index Merge is not applicable to full-text indexes. We plan to extend it to cover these in a future MySQL release.

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.

Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout