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.
This is just my $0.02...
Thanks,
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
-----Original Message-----
From: Ben Clewett [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 14, 2008 11:57 AM
To: Richard
Cc: mysql@lists.mysql.com
Subject: Re: select unique ?
Try:
SELECT DISTINCT Colour FROM table;
Or, if you want to do it correctly:
SELECT Colour FROM table GROUP BY color;
Richard wrote:
> Hello,
>
> I don't know if it is possible to do this with mysql alone ...
> Here goes :
> I've got a database list which is like to following :
>
> Num | Name | Colour
> -------------------------------------------
> 1 | Harry | Red
> 2 | Tom | Blue
> 3 | Jane | Green
> 4 | Philip | Red
> 5 | Sarah | Red
> 6 | Robert | Blue
>
>
> And from this table I wish to get a list of used colours.
>
> The correct answer would be :
>
> Colour
> ---------
> Red
> Blue
> Green
>
> The answer I don't want :
>
> Colour
> --------
> Red
> Blue
> Green
> Red
> Red
> Blue
>
> How would I achieve the first result with mysql ? Is it possible?
>
>
> Thanks in advance,
>
> Richard
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
