jeudi 18 décembre 2014

Can MySQL Index Merge an Composite index with a single column index?


I am tuning a query and currently have an index as following :



Index 1 : (col1, col2)


An example query would be :



SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2';


I have created an index on col3 so the query uses that as it is the most selective. However, I am wondering if it is able to use the first key in the composite index as an index merge. I would imagine this is not possible as far as an intersecting index merge but just making sure. I could not find this in the MySQL documentation and I am using 5.5.


To verify my results : I have created a single column index on col1 so my new indexes are as follows :



Index 1 : (col1, col2)
Index 2 : (col3)
Index 3 : (col1)


The query now index merges Index 2 and Index 3.


Is it true that you cannot index merge the first key in a composite index with another single column index?





Aucun commentaire:

Enregistrer un commentaire