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.

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 30 DAY ) AND
`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 
("0815.ru","123.com","123mail.org","126.com","139.com","13hotmail.com")

Question is: why?

share|improve this question
1  
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

1 Answer 1

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 at 0:00

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.