help for opimizing a query

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

Re: help for opimizing a query

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.

Re: help for opimizing a query

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

Re: help for opimizing a query

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

How slow is slow? Does it take more than a second? Also, is there an
index on Email? This may make a difference - although when I tested
your query on a (smaller) sample it was actually slower WITH the index!

Re: help for opimizing a query

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

Over the last few weeks there have been some pretty good tutorials on
optimizing mysql queries on DevShed: http://www.devshed.com/c/b/MySQL/

I suggest reading those and trying some of the methods they suggest.