|
|
Posted by J.O. Aho on 08/17/06 03:12
Bob Stearns wrote:
> MH wrote:
>> hi everyone !
>>
>> I am working on a MySQL database version 3.23.58 .
>>
>> I want to get the customers who have the same email address. Here is
>> what I did :
>>
>> SELECT C1.Username, C1.Email, COUNT(*) FROM customer AS C1, customer
>> AS C2 WHERE C1.Email = C2.Email GROUP BY C1.CustomerID HAVING COUNT(*)
>> > 1
>>
>> The query does its job but is veerryyyyy slow to execute although I
>> have only 5000 records in my database.
>>
>> Any idea how to optimize this query ?
>>
>> Thanks a lot !
>>
>> MH
>>
> Try the following:
>
> SELECT C1.Username, C1.Email, COUNT(*) FROM customer AS C1
> WHERE c1.email in (
> SELECT C1.Email, COUNT(*) FROM customer AS C2
> GROUP BY C2.Email
> HAVING COUNT(*) > 1)
>
> I don't use MySql, but I believe I've read that versions before 4.xxx,
> for some non-zero value of xxx do not support subselects. In that case
> maybe a temporary table and an equi-join would do.
Version 4.1.x and later supports subquery, those X.xxx type of version numbers
aren't that common in the unix world, mostly microsoft world that uses them
and has the draw back you don't know how big the difference between two
versions may be by just look at the version number.
//Aho
Navigation:
[Reply to this message]
|