MySQL Optimization Index Merge (indexed merging)

Before MySQL5.0, a table in a statement can use only one index and cannot use multiple indexes at the same time. However, starting with 5.1, the index merge optimization technique is introduced, and multiple indexes can be used for the same table. Having understood the index merge technology, we know how to index the table.

Related documents: http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html ( Note that there are several errors in the document )

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 the index scans from a single table, it does not the merge scans acrossmultiple tables.

EXPLAINin output, the Index Merge method appears as in the index_merge 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 tho SE indexes.

Index Merge: A range scan of multiple indexes on the same table can merge the results into three types: union, intersection, and their various combinations.

Examples:

SELECT * FROMtbl_nameWHEREkey1= Ten ORkey2= 20; SELECT * FROMtbl_nameWHERE (key1= Ten ORkey2=) andnon_key= 30; SELECT * from T1, T2 WHERE (t1.key1In (or T1).key2Like 'value% ') and T2.key1=t1.some_col; SELECT * from t1, T2 WHERE t1.key1=1 and (T2.key1=t1.some_colOR T2.key2=t1.some_col2);

The document here is wrong: The last SELECT statement, T2.key1=t1.some_col or t2.key2=t1.some_col2, because the or is used here, it is not possible to use a composite index.

The Index Merge method has several access algorithms (seen in the Extra field of EXPLAIN output):

• Using intersect(...)

• Using union(...)

• Using sort_union(...)

Depending on how the index is merged, the merge method used in the explain results is displayed.

Generally, and the Index merge is not necessarily a good thing. For example, there is a use of the implementation of the Intersect plan, indicating that our index is not the best establishment, generally can be established by the index to further optimize , you can refer to the article: https://www.percona.com/blog/ 2009/09/19/multi-column-indexes-vs-index-merge/

MySQL Optimization Index Merge (indexed merging)