MySQL Forums
Forum List  »  InnoDB

Re: why delete will block by select?
Posted by: Rick James
Date: December 17, 2014 06:38PM

Sorry, I should have asked for this, too:
SHOW CREATE TABLE phoenix_adspace_execution_byhour_oftime \G

delete
     from  phoenix_adspace_execution_byhour_ofregion
    where  adspace_time_id in (
        SELECT  id
            from  phoenix_adspace_execution_byhour_oftime
            where  createdTime >= str_to_date('2014-12-15 16', '%Y-%m-%d %H:%i:%s')
              and  createdTime  < str_to_date('2014-12-15 16', '%Y-%m-%d %H:%i:%s')
              and  ( adspaceId = 0
                      or  adspaceId = 6841
                      or  adspaceId = 8133
Questions:

* Am I wrong, or does that range on createdTime eliminate all possible rows?

* Regardless of how many rows in the SELECT in the IN(), the DELETE will scan the entire table checking each adspace_time_id. That is probably the killer.

* Can you turn that DELETE + subquery into a multi-table DELETE? That _will_ help significantly, probably avoiding the locks...

In general, do not use this construct:
IN ( SELECT ... )
Whenever possibly, convert to a JOIN.
Even 5.6's use of "MATERIALIZED" only helps the inefficiency, it does not really 'fix' it.

I just tried a query similar to yours. It did a full scan of the outer table, then reached into the empty 'MATERIALIZED' temp table once per row. Yuck.

Options: ReplyQuote


Subject
Views
Written By
Posted
2289
h h
December 15, 2014 07:53PM
1142
December 16, 2014 04:32PM
1169
h h
December 16, 2014 09:37PM
Re: why delete will block by select?
1005
December 17, 2014 06:38PM
1023
h h
December 17, 2014 09:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.