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
