From: Richard (mysql_listghz.fr)
Date: Sat Feb 16 2008 - 05:45:59 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Price, Randall a écrit :
> 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:benclewett.org.uk]
> Sent: Thursday, February 14, 2008 11:57 AM
> To: Richard
> Cc: mysqllists.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
>>
>

Thanks :) It works great with the SELECT DISTINCT, and if it's faster
than I will keep to this solution :)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]