RE: select unique ?

[Randall Price]

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
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1