Re: index selection

[Shlomi Noach]

While KimSeong is right, of course, in stating that there shouldn't be redundant indexes, the question still remains: why would MySQL choose one of the indexes over the other at one time, and vice versa on other times.

I would suggest it has to do with the selectivity estimations of the two indexes. Depending on your storage engine and on the characteristics of the DML statements on your table, the two indexes may be fairly accurately estimated with regard to selectivity, or one may and other not, or they may "guess right" at some values but wrong for other values.
Since it is always a game of estimations in the optimizer (it rarely actually "know" the right answer), it may come that it would prefer varying indexes at times, even if both index the same column.

Hope this helps,
Shlomi