List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:February 16 2008 4:54pm
Subject:Re: select unique ?
View as plain text  
Hi,

On Feb 15, 2008 10:26 AM, Price, Randall <randallp@stripped> wrote:
> Since both of these work, I was wondering which one would be faster.
>
> Here is an EXPLAIN on a similar test I did on one of my test tables.
>
> (NO index on field1, WinXP, MySQL 5.0.41-community-nt, SQLyog query
> window)
>
>
>     SELECT COUNT(*) FROM tblClients
>     (1660 row(s) returned)
>     (0 ms taken)
>
> RESET QUERY CACHE
>
>     SELECT DISTINCT field1 FROM tblClients
>     (130 row(s) returned)
>     (0 ms taken)
>
>
>     EXPLAIN SELECT DISTINCT field1 FROM tblClients
>     /* 1457 rows, Using temporary */
>
>
> RESET QUERY CACHE
>
>     SELECT field1 FROM tblClients GROUP BY field1
>     (130 row(s) returned)
>     (16 ms taken)
>
>
>     EXPLAIN SELECT field1 FROM tblClients GROUP BY field1
>     /* 1457 rows, Using temporary; Using filesort */
>
>
> It appears that the SELECT DISTINCT did not have to use the filesort.
> So that should be faster, which confirms what I see here.

That's because MySQL automatically sorts GROUP BY queries by the
group-by columns.  If you add ORDER BY NULL, the two queries should be
exactly equivalent.  But I'd just use DISTINCT :-)

Baron
Thread
select unique ?Richard14 Feb
  • Re: select unique ?peter lovatt14 Feb
  • Re: select unique ?Ben Clewett14 Feb
    • RE: select unique ?Randall Price15 Feb
      • Re: select unique ? (solved thankyou :))Richard16 Feb
      • Re: select unique ?Baron Schwartz16 Feb