MySQL Forums
Forum List  »  Optimizer & Parser

Re: Index distribution stats
Posted by: Gabriel Tataranu
Date: May 13, 2007 05:42PM

Hate to reply to my own message but information here may be of interest.

Basically the problem lies with the crude method user for evaluating the value distribution in the indices. The index I had a problem with was having fair cardinality for values other than 0, but most values where 0. That skewed the estimate to more than double rendering a good index useless. I wish for a better way to estimate value distribution or at least mysql should filter the common values - not unlike it does for fulltext indices.
In my case I've just moved all 0 values to 255 (lucky me that value was unused), run ANALYZE TABLE and move the values back to 0. The distibution is now skewed on the low side (index looks better that it is) but not by a large margin, so happy ending. Of course that may not be possible all the time, but let's hope somebody an MYSQL AB is listening.

Options: ReplyQuote


Subject
Views
Written By
Posted
2801
May 12, 2007 09:54PM
Re: Index distribution stats
2107
May 13, 2007 05:42PM


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.