Preface

   mysql index merge is not a new feature. It has been implemented as early as mysql5.0 version. The reason why I wrote this blog post is because many people still retain the misconception that a SQL statement can only use one index. This article will use some examples to illustrate how to use index merging.

What is index merge

   Let's look at the description of index merging in the mysql 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 index scans from a single table; it does not merge scans across multiple tables.

   According to the instructions in the official document, we can understand:

   1.Index merging is to merge the range scans of several indexes into one index.

   2.When indexes are merged, the indexes will be merged, intersection or first intersection and then union operation, in order to merge into one index.

   3.These indexes that need to be merged can only belong to one table. It is not possible to merge indexes on multiple tables.

How to determine the use of index merge

   When using explain to operate sql statements, if index merging is used, index_merge will be displayed in the type column of the output content, and all indexes used will be displayed in the key column. as follows:

Example of using index merge

DATA TABLE STRUCTURE

mysql> show create table test\G
*************************** 1.row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1_part1` int(11) NOT NULL DEFAULT '0',
  `key1_part2` int(11) NOT NULL DEFAULT '0',
  `key2_part1` int(11) NOT NULL DEFAULT '0',
  `key2_part2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `key1` (`key1_part1`,`key1_part2`),
  KEY `key2` (`key2_part1`,`key2_part2`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

DATA

mysql> select * from test;
+----+------------+------------+------------+------------+
| id | key1_part1 | key1_part2 | key2_part1 | key2_part2 |
+----+------------+------------+------------+------------+
|  1 |          1 |          1 |          1 |          1 |
|  2 |          1 |          1 |          2 |          1 |
|  3 |          1 |          1 |          2 |          2 |
|  4 |          1 |          1 |          3 |          2 |
|  5 |          1 |          1 |          3 |          3 |
|  6 |          1 |          1 |          4 |          3 |
|  7 |          1 |          1 |          4 |          4 |
|  8 |          1 |          1 |          5 |          4 |
|  9 |          1 |          1 |          5 |          5 |
| 10 |          2 |          1 |          1 |          1 |
| 11 |          2 |          2 |          1 |          1 |
| 12 |          3 |          2 |          1 |          1 |
| 13 |          3 |          3 |          1 |          1 |
| 14 |          4 |          3 |          1 |          1 |
| 15 |          4 |          4 |          1 |          1 |
| 16 |          5 |          4 |          1 |          1 |
| 17 |          5 |          5 |          1 |          1 |
| 18 |          5 |          5 |          3 |          3 |
| 19 |          5 |          5 |          3 |          1 |
| 20 |          5 |          5 |          3 |          2 |
| 21 |          5 |          5 |          3 |          4 |
| 22 |          6 |          6 |          3 |          3 |
| 23 |          6 |          6 |          3 |          4 |
| 24 |          6 |          6 |          3 |          5 |
| 25 |          6 |          6 |          3 |          6 |
| 26 |          6 |          6 |          3 |          7 |
| 27 |          1 |          1 |          3 |          6 |
| 28 |          1 |          2 |          3 |          6 |
| 29 |          1 |          3 |          3 |          6 |
+----+------------+------------+------------+------------+
29 rows in set (0.00 sec)

CASES OF USING INDEX MERGING

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or key2_part1=4\G
*************************** 1.row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index_merge
possible_keys: key1,key2
          key: key1,key2
      key_len: 8,4
          ref: NULL
         rows: 3
        Extra: Using sort_union(key1,key2); Using where
1 row in set (0.00 sec)

CASES WHERE INDEX MERGE IS NOT USED

mysql> explain select * from test where (key1_part1=1 and key1_part2=1) or key2_part1=4\G
*************************** 1.row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: key1,key2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 29
        Extra: Using where
1 row in set (0.00 sec)

   From the above two cases, you can find that the SQL statement of the same mode may sometimes use the index, and sometimes cannot use the index. Whether the index can be used depends on whether the mysql query optimizer analyzes the statistical data and considers it to be faster to use the index.

   Therefore, simply discussing whether a SQL can use an index is a bit one-sided, and data needs to be considered.

Precautions

   Versions before mysql5.6.7 follow the principle of range first. In other words, when a continuous segment of an index contains all the data that meets the query requirements, even if index merging can provide efficiency, index merging is no longer used. for example:

mysql> explain select * from test where (key1_part1=1 and key1_part2=1) and key2_part1=1\G
*************************** 1.row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: key1,key2
          key: key2
      key_len: 4
          ref: const
         rows: 9
        Extra: Using where
1 row in set (0.00 sec)

   There is only one result that meets the requirements of the query above, and this record is included in the index key2.

   You can see that this SQL statement uses the key2 index. But this is not the fastest way to execute. In fact, the index key1 and index key2 are indexed and merged, and after taking the intersection, it is found that only one record is suitable. The query efficiency should be faster.

   tips: This sql statement has not been verified in the version after mysql5.6.7, the above is a theoretical derivation. If you are interested, you can verify the version after mysql5.6.7.