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.