Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Query 1:

select distinct email from mybigtable where account_id=345

takes 0.1s

Query 2:

Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above result>)

takes 0.2s

Query 3:

Select count(*) as total from mybigtable where account_id=123 and email IN (select distinct email from mybigtable where account_id=345)

takes 22 minutes and 90% its in the "preparing" state. Why does this take so much time.

Table is innodb with 3.2mil rows on MySQL 5.0

share|improve this question
    
Just curious, can you add the EXPLAIN output on query 3? –  Derek Downey Mar 7 '12 at 17:16
add comment

4 Answers

up vote 4 down vote accepted

In Query 3, you are basically executing a subquery for every row of mybigtable against itself.

To avoid this, you need to make two major changes:

MAJOR CHANGE #1 : Refactor the Query

Here is your original query

Select count(*) as total from mybigtable
where account_id=123 and email IN
(select distinct email from mybigtable where account_id=345)

You could try

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A;

or maybe the count per email

select email,count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A group by email;

MAJOR CHANGE #2 : Proper Indexing

I think you have this already since Query 1 and Query 2 run fast. Make sure you have a compound index on (account_id,email). Do SHOW CREATE TABLE mybigtable\G and make sure you have one. If you don't have it or if you are not sure, then create the index anyway:

ALTER TABLE mybigtable ADD INDEX account_id_email_ndx (account_id,email);

UPDATE 2012-03-07 13:26 EST

If you want to do a NOT IN(), change the INNER JOIN to a LEFT JOIN and check for the right side being NULL, like this:

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    LEFT JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
    WHERE tbl345.email IS NULL
) A;

UPDATE 2012-03-07 14:13 EST

Please read these two links on doing JOINs

Here is a great YouTube Video where I learned to refactor queries and the book it was based on

share|improve this answer
    
There is an index on email as well as account_id (its not composite though). Join did fix it, but how do I make queries like NOT IN() work (same example above, to get all email not in the other account) with joins ? –  Stewie Mar 7 '12 at 18:23
    
I updated my answer to address your NOT IN() comment. –  RolandoMySQLDBA Mar 7 '12 at 18:27
1  
You should use the compound index. It will shorten the retrieval time by fetching data from the indexes only. –  RolandoMySQLDBA Mar 7 '12 at 18:28
    
thanks for the above solution, I haven't used derived queries and am getting this error: Every derived table must have its own alias .. –  Stewie Mar 7 '12 at 18:31
    
I fixed it. Please try it now... –  RolandoMySQLDBA Mar 7 '12 at 18:33
show 1 more comment

In MySQL, subselects within the IN clause are re-executed for every row in the outer query, thus creating O(n^2). The short story is, don't use IN (SELECT).

share|improve this answer
    
+1 for describing the running time in O(n^2) notation along with your short-story conclusion. –  RolandoMySQLDBA Mar 12 '12 at 18:17
add comment
  1. Do you have an index on account_id?

  2. The second problem may be with the nested sub-queries which have terrible performance in 5.0.

  3. GROUP BY with a having clause is faster than DISTINCT.

  4. What are you trying to do which may be better done through joins in addition to Item #3?

share|improve this answer
add comment

There is a lot of processing involved when handling an IN() subquery such as yours. You can read more about it here.

My first suggestion would be to attempt to re-write the subquery into a JOIN instead. Something like (not tested):

SELECT COUNT(*) AS total FROM mybigtable AS t1
 INNER JOIN 
   (SELECT DISTINCT email FROM mybigtable WHERE account_id=345) AS t2 
   ON t2.email=t1.email
WHERE account_id=123
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.