Re: Strange DISTINCT !

On Mon, 20 Aug 2001, Jean-Christophe Boggio wrote:

> Hello, can someone explain to me why this query is so slow :
>
> select distinct t.idmembre,p.datecrea
> from tmp_stat t,prefs p
> where p.idmembre=t.idmembre
> limit 5;
>
> And this one is so fast :
>
> select t.idmembre,p.datecrea
> from (select distinct idmembre from tmp_stat) as t,
> prefs p
> where p.idmembre=t.idmembre
> limit 5;
>
> (I currently have idmembre as an index on tmp_stat and prefs)

I'd suggest looking at the explain output for the two queries,
however, I believe the first query is likely to result in a sort
and unique step and the second is going to probably use the index
to distinct on tmp_stat. I think that *possibly* if you used
select distrinct p.idmembre, p.datecrea and made an index on (idmembre,
datecrea) on prefs you could possibly see an improvement but I really
don't know.

In addition, these two queries may not do the same thing. The first
will unique over both membre and datecrea whereas I think the second
will not, so if you had two prefs rows with the same idmembre and
datecrea, I believe the first will give one row and the second two.