Help optimizing this mysql query

Question

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.

The Scrum Meister · Accepted Answer · 2011-02-20 11:46:00Z

2

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

answered Feb 20, 2011 at 11:46

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeremy Roy Over a year ago

Excellent work. Let me tell you something. In my query for my existing table, it was taking 27,9810 secs. Your query is giving me the same thing in 0,0306 secs. Now that's called optimization, isn't it? Cool! Thanks a lot.

Jeremy Roy Over a year ago

One question here, I see you are always doing like FROM member m whereas I have always done FROM member AS m. Is there any difference between these two or are all the same?

awm Over a year ago

The word AS is optional. :) Good only for clarity.

awm · Accepted Answer · 2011-02-20 11:43:02Z

1

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`

answered Feb 20, 2011 at 11:43

awm

6,58027 silver badges24 bronze badges

5 Comments

Jeremy Roy Over a year ago

Thanks awm, it looks great. However, it is removing the duplicate entries, I want them as well :) A way out?

awm Over a year ago

Yes, you'll only get one row per email this way. Using the group_concat, you can see all of the ids of the duplicate emails, though. Or use the Scrum Meister's solution.

Jeremy Roy Over a year ago

yes, scrum meister's solution is working, I have just found. group_concat is returning a blob. :)

awm Over a year ago

Group_concat does return a blob. And in the blob is a comma-separated list of ids. If you're not seeing the ids, test it by casting the result to char: SELECT CAST(GROUP_CONCAT(id) AS CHAR) ...

Jeremy Roy Over a year ago

This is great awm, honestly, I didn't see the comma separated list of ids. I'm sure I'm going to use this more in the future. Great to learn that, thanks again.

Dave Rix · Accepted Answer · 2011-02-20 11:55:57Z

0

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

answered Feb 20, 2011 at 11:55

Dave Rix

1,6891 gold badge11 silver badges17 bronze badges

2 Comments

Jeremy Roy Over a year ago

Thanks again Dave. Nope, member table has no index on the email field. Scrum's solution was pretty fast, though :)

Dave Rix Over a year ago

Looking at @Scrum's solution, it's essentially the same as mine, the nested query will create a temp table internally. You may find adding the index will help further, especially when the table gets /really/ large!

Chris · Accepted Answer · 2011-02-20 11:43:23Z

-1

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

answered Feb 20, 2011 at 11:43

Chris

27.5k49 gold badges208 silver badges358 bronze badges

2 Comments

The Scrum Meister Over a year ago

Will only return 1 row per email.

Jeremy Roy Over a year ago

Thanks Chris, your previous solution was longer count(*) is faster than count(id). This solution is removing the duplicate emails, like awm's first solution, which I need them as well. Thanks anyway :)

Tim · Accepted Answer · 2011-02-20 11:44:14Z

-1

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

answered Feb 20, 2011 at 11:44

Tim

13.3k36 gold badges111 silver badges159 bronze badges

1 Comment

The Scrum Meister Over a year ago

Will only return 1 row per email.

Collectives™ on Stack Overflow

Help optimizing this mysql query

5 Answers 5

3 Comments

5 Comments

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

5 Comments

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related