Can [My]SQL combine multiple overlapping composite indexes?

Question

Basically, if I have a table with composite indices (colA, colB, colC) and (colB, colD, colE), is MySQL able to take advantage of their overlap on colB to combine them and speed up a query involving colA, colB and colD, even if there is no single index covering these three particular columns?

I tried using EXPLAIN on a test case like this, but even though it recognized both indices as possible keys, it only used the first one. However, I do not know if this was because it is unable to make the combination or because the optimizer didn't think it was worth it given the number of rows.

I would expect it should be able to, but couldn't find any straight confirmation around. Thanks for your input.

Answer 1

I can't find any definitive answer either, but I think the answer is that it depends on the query and the data in the tables. A multiple-column index can be used if the query refers to any of the left-most columns. With your (ColA, ColB, ColC) index, the index may be used for queries which refer to (ColA), (ColA, ColB) or (ColA, ColB, ColC). The same principle is true for your other index.

When two separate indexes are available, the MySQL optimizer will choose between performing an intersection based on both indexes, or using a single index, and filtering out the remaining results. I suspect that the same will be true if both those separate indexes are multi-column indexes, and if the query refers to the left-most part of the indexes (as your example does with ColA, ColB, ColC, ColD.

You might find these links of use (but I don't think any of them will provide a definitive answer):

MySQL Manual
Multiple-Column Indexes
How MySQL Uses Indexes

MySQL Performance Blog
Multi Column indexes vs Index Merge