List:General Discussion« Previous MessageNext Message »
From:Richard Date:February 16 2008 11:45am
Subject:Re: select unique ? (solved thankyou :))
View as plain text  
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:ben@stripped] 
> Sent: Thursday, February 14, 2008 11:57 AM
> To: Richard
> Cc: mysql@stripped
> 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 :)

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