I have a table with two indexes, one of which is the faster covering index for a certain query. However, mySQL (5.1) is not choosing the correct index. I have looked at the explain for this query and done some speed tests and it makes a big difference if you force the key.
Is there any way of examining how it picks the index and what criteria it is basing this on?
This will use the index you need (in case it's usable):
SELECT *
FROM table FORCE INDEX (myindex)
MySQL collects and uses table statistics to estimate the cardinality of the indexes.
It keeps it in information_schema.statistics.
Of two indexes that may be applied for filtering, MySQL chooses the one with greater cardinality.
If there are two indexes with big cardinality, it can choose INDEX MERGE over both of them.
In one index can serve WHERE condition and another one can serve ORDER BY, MySQL seems to prefer the first one
In the latter case, however, it's better to create a composite index over both columns, which can serve both clauses.