Help optimizing this mysql query

Well friends, I have got this query which works but is very long for the execution. I was wonderin开发者_高级运维g whether there is a faster way to achieve this.

SELECT id, email FROM member WHERE email IN 
(SELECT email FROM member GROUP BY email HAVING count( * ) >1 ) 
ORDER BY `email` ASC

Basically, there are entries where the same email is appearing more than once, and I just wanted to have those rows returned where there is duplicate entries of 'email'.

The above query works in that direction, but is painfully long.

Cheers.

You can group the results first, then join them to the member table to insure only rows with duplicate emails will show.

SELECT m.id, m.email
FROM member m JOIN (
    SELECT email 
    FROM member 
    GROUP BY email 
    HAVING COUNT(*) > 1
  ) g ON m.email = g.email
ORDER BY m.email ASC

Your query is slow because of the nested select, which gets recomputed for every row. The best solution is to rewrite your algorithm a bit so that you can use a query like this:

SELECT id, email 
FROM member GROUP BY email
HAVING count( * ) >1
ORDER BY `email`

Unfortunately, the id you get back will be a random choice among each group. This may be a more useful query:

SELECT GROUP_CONCAT(id), email 
FROM member GROUP BY email
HAVING count( * ) >1
ORDER BY `email`

Can you do this in two stages? First create a temporary table containing all the emails with > 1 occurance, then join the main table to the temp table through the email field...

If your member table has an index on the email field, this should be pretty fast.

CREATE TEMPORARY TABLE ddd
SELECT email, count(*) as cnt FROM member GROUP BY email HAVING cnt>1;

SELECT * FROM ddd
INNER JOIN member USING (email);

You are doing two queries when you only need to do one

SELECT id, email 
FROM member
GROUP BY email 
HAVING count( * ) > 1
ORDER BY `email` ASC

select id,email,count(*) as n from member group by id having n > 1;