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 at 20:12
    
MySQL 5.6 optimizes subqueries much better than earlier versions. I would recommend upgrading. –  Morgan Tocker May 12 at 21:19
    
Do you have an index on mail? –  Mihai May 13 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

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.