Re: Index distribution stats

[Gabriel Tataranu]

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.