MySQL5.6 supports Index Merge Optimization Index Optimization

On MySQL, the index Merge function is added, and two indexes can be used for one SQL statement.

The following is an example:

1. Mysql> select * from t;
2. + ------ +
3. | A | B |
4. + ------ +
5. | 1 | 24 |
6. | 4 | 1 |
7. | 1 | 20 |
8. | 4 | 26 |
9. | 5 | 10 |
10. | 4 | 13 |
11. | 1 | 11 |
12. | 2 | 15 |
13. | 3 | 27 |
14. | 1 | 25 |
15. | 4 | 10 |
16. | 2 | 21 |
17. | 3 | 4 |
18. | 2 | 1 |
19. | 2 | 4 |
20. | 5 | 5 |
21. | 1 | 27 |
22. | 2 | 16 |
23. | 5 | 17 |
24. | 2 | 30 |
25. | 5 | 14 |
26. | 4 | 1 |
27. | 5 | 26 |
28. | 3 | 14 |
29. | 1 | 21 |
30. | 3 | 2 |
31. | 1 | 30 |
32. | 5 | 6 |
33. | 2 | 13 |
34. | 5 | 4 |
35. | 1 | 21 |
36. | 2 | 27 |
37. | 2 | 14 |
38. | 4 | 26 |
39. | 2 | 10 |
40. | 3 | 14 |
41. | 5 | 23 |
42. | 2 | 12 |
43. | 5 | 7 |
44. | 3 | 25 |
45. + ------ +
46. 40 rows in set (0.02 sec)

Let's take a look at the performance of MySQL5.5:

 

Indexes cannot be used. After optimization, only one index can be used.

Let's look at the performance on 5.6:

The two indexes are used together and the index merging method is adopted.

Reference manual: