Help optimizing this mysql query

Questions

Well friends, I have got this query which works but is very long for the execution. I was wondering 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.

Answers

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

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/5056786/help-optimizing-this-mysql-query

Related