Re: index selection
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
Subject
Views
Written By
Posted
6457
August 07, 2008 06:01AM
2595
August 07, 2008 07:07PM
Re: index selection
2629
August 30, 2008 11:48AM
2477
August 31, 2008 11:16PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.