How to use mysql index merge

Index merging is an intelligent algorithm provided by MySQL at the bottom layer. Understanding the index merge algorithm will help us create better indexes.

An index merge retrieves rows by performing multiple range scans and merging their result sets. Only index scans from a single table are merged, not index scans across multiple tables. The merge will produce three forms of underlying scans: unions, intersections, and unions-of-intersections (intersections first and then merge).

The following four examples will result in an index merge:

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);

Index Merge has the following known limitations:

1. If the query statement contains a complex WHERE clause with severe AND/OR nesting and MySQL does not choose the best plan, you can try using the following identifier conversion:

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

2. Index merging is not applicable to full-text indexing.

In the EXPLAIN statement output, index merges appear as "index_merge" in the type column, in which case the key column contains the list of indexes used.
The index merge access method has several algorithms, which are shown in the Extra field of the EXPLAIN statement output:

Using intersect(...)
Using union(...)
Using sort_union(...)

These algorithms are described in more detail below. The optimizer chooses between different index merge algorithms and other access methods based on cost estimates of the various available options.

Index Merge Intersection Algorithm

The Index Merge Intersection algorithm performs simultaneous scans of all used indexes and produces the intersection of the sequence of rows received from the merged index scans.
This algorithm works when the WHERE clause is converted into multiple range conditions on different index keys connected by AND, and the conditions are one of the following:

1. For this form of N-part expression, the index includes exactly N fields (all index fields are covered), N>=1, if N is greater than 1, it is a composite index:

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN.

2. Any range condition on the primary key of the InnoDB table.

example:

SELECT * FROM innodb_table
WHERE primary_key < 10 AND key_col1 = 20;
SELECT * FROM tbl_name
WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

Index Merge Union Algorithm

This algorithm is similar to the Index Merge Intersection algorithm and is applicable when the WHERE clause is converted into multiple range conditions on different index keys connected by OR, and the conditions are one of the following three:

1. For this form of N-part expression, the index includes exactly N fields (all index fields are covered), N>=1, if N is greater than 1, it is a composite index:

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN.

2. Any range condition on the primary key of the InnoDB table.
3. Meet the conditions of Index Merge Intersection algorithm.

example:

SELECT * FROM t1
WHERE key1 = 1 OR key2 = 2 OR key3 = 3;
SELECT * FROM innodb_table
WHERE (key1 = 1 AND key2 = 2)
OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

Index Merge Sort-Union Algorithm

This algorithm works when the WHERE clause is converted into multiple range conditions on different index keys connected by OR, but does not conform to the Index Merge Union algorithm. The difference between the Index Merge Sort-Union and Index Merge Union algorithms is that Index Merge Sort-Union must first get the row ids of all rows and sort them before returning any rows.

example:

SELECT * FROM tbl_name
WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

This is the end of this article about the use of MySQL index merge. For more relevant MySQL index merge content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!