Imagine a table where there are separate indexes on columns a
and b
.
The query runs somewhat like this:
SELECT col1, col2 FROM <table> WHERE a = 'value_1 AND b = 'value_2';
Now, does this query always use the indexes on both the columns? If so, what is the mechanism? And if not, is there any condition which MySQL checks to decide which index to use when more than one are available?
Best Answer
MySQL is capable of doing an INDEX MERGE which allows MySQL to search on both indexes and merge the results. Of course, the MySQL Query Optimizer is the arbitrator of this process.
From my perspective, just looking at your query and its WHERE clause, I would create a compound index on your table and an additional index. Which compound index,
(a,b)
or(b,a)
?I would next check the cardinality of each column as follows:
Based on these counts
For more information, please read the following links: