Re: select unique ? (solved thankyou :))

Richard Sat, 16 Feb 2008 03:46:39 -0800

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:[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

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: select unique ? (solved thankyou :))

Reply via email to