Posted by Bob Stearns on 08/16/06 21:21

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.

• Next in forum: Re: help for opimizing a query
• Prev in forum: help for opimizing a query
• Thread view: Re: help for opimizing a query

[Reply to this message]