Re: Use of index for SELECT DISTINCT

[Ulf Wendel]

Dominique PRUNIER wrote:
> a: integer
> b: varchar(128)
>
> Let's say that b has only a few distinct values
> but the table has MANY rows (2 000 000). The
> column b is indexed, with of course a VERY low
> cardinality.
>
> Why optmimizer will always scan the 2 000 000
> (using index on b though) to execute query SELECT
> DISTINCT b FROM table while it knows there are
> only a few distincts values from the index ?

Dominique,

are you saying that MySQL is using an index scan? What's wrong with this strategy. MySQL should have the number of distinct values in it's statistics but it does not know the distinct values. Thus, scanning the smallest datastructure that's available to find out the distinct values (the index instead of the table) makes perfectly sense to me.

Ulf