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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I run this query to update "domains" table based on "log" table:

UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
(SELECT log.mail as domain_name FROM log WHERE
`time` <= DATE_SUB( CURDATE( ) , INTERVAL 15 DAY )
group BY domain_name
HAVING count(*) > 200)

While the subquery itself runs very fast (0.6 sec) with a result set of only 6 items the update never ends (not after hours). But if I run the update-query with a item list instead of a subquery it successfully ends after 0.02 seconds:

UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN 

Question is: why?

share|improve this question
can you show the execution plan? – James Anderson May 12 '14 at 20:12
MySQL 5.6 optimizes subqueries much better than earlier versions. I would recommend upgrading. – Morgan Tocker May 12 '14 at 21:19
Do you have an index on mail? – Mihai May 13 '14 at 0:08

The subquery will be executed for each row from the outer query, and no index will be used. In addition, 0.6 sec is "relatively" long. For example, if you have 1000 rows in domain table, then expect more them 600 sec (10 minutes).

In the second query, MySQL is able to use the index on 'domain'. This is a feature of using "IN" instead of subquery.

share|improve this answer
The sub-query for an IN condition does not need to be executed for each row of the outer query. In fact in can't be, because it has to be executed before the outer query in order to limit that. If MySQL really runs the query for the IN condition on each and every row from than the query optimizer is even worse than I thought. – a_horse_with_no_name Jan 4 '15 at 0:00
I think it depends on the version of MySQL. MySQL prior to 5.6 won't run the subquery only once. – Jehad Keriaki Oct 26 '15 at 19:00

Your Answer


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.