MySql query taking long time

Question

I have the following tables:

Table 1 : Contacts
Fields : id  first_name

Values : 

1    Reeta
2    Rohan
3    John 

Table 2 : email (it contains contact_id of contacts table)
Fields : id   contact_id  email_address

Values :

1  1  r@gmail.com
2  2  r@gmail.com
3  3  j@gmail.com

I want to display all duplicates by email. Like this:

cont_id  first_name  email_address

   1        Reeta  r@gmail.com
   2        Rohan  r@gmail.com

Here is my query :

select contact_id 
from contacts 
where email_address IN (
  SELECT S.email_address 
  FROM  contacts R
  INNER JOIN email
    ON R.id = S.contact_id 
  Group By email_address
  Having Count(S.id) > 1
);

The query takes long time to execute with large number of records. However the inner query works faster but not the outer one. Please Help.

What indexes have you created? id, contact_id, email_address need indexes — mseifert, Feb 25 '14 at 19:05

score 0 · Answer 1 · answered Feb 25 '14 at 19:06

I would move your INNER JOIN outside of your subquery.

SELECT
    c.contact_id,
    c.first_name,
    e.email_address
FROM contacts c
INNER JOIN email e ON c.id = e.contact_id
WHERE e.email_address IN (
    SELECT email_address
    FROM contacts
    GROUP BY email_address
    HAVING COUNT(id) > 1
);

You could also implement MySQL's EXPLAIN to get a better idea what's choking your query.

score 0 · Answer 2 · answered Feb 25 '14 at 19:09

Another way to do it, which should be quicker, is something like this:

select email.email_address, group_concat(contacts.contact_id)
from contacts inner join email on contacts.contact_id=email.contact_id
group by email.email_address 
having count(contacts.contact_id) > 1;

Now, you're getting exactly what you want; the only thing is, the contact IDs will be concatenated as a comma-separated string. But you'll know which email addresses are non-unique.

You should also have indexes on contact_id in all tables (because you join on these fields), and probably email_address as well (since you search on it).

MySql query taking long time

2 Answers2