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.