Hi!
I work on the MySQL Team @ Oracle. I have two suggestions for improving this fix:
The first is that only MySQL 5.6+ can OPTIMIZE an IN subquery[1]. The description says that you arrived at (4), but for compatibility with earlier MySQL versions, may I suggest rewriting to use a join, similar to solution (5)?
The second suggestion I have it that there is some risk in using this query pattern with STATEMENT-BASED replication:
1) CREATE TEMPORARY TABLE activity_to_delete SELECT ...;
2) DELETE a FROM sta_activity a JOIN activity_to_delete atd on a.id = atd.id;
The reason is that when MySQL has a replication slave, it can not safely stop/crash/restart in between steps 1 and 2. In doing so, replication will stop because statement (2) does not know how to apply. This will then require ops intervention to resolve, and worst-case a re-image from the master!
Using ROW-BASED replication avoids this problem, and also reduces the amount of locking that the mysql server needs to apply for these statements (when used in conjunction with READ-COMMITTED as Stash uses).
[1] As an aside, MySQL 5.6 may also not be able to OPTIMIZE this subquery in current form. Explained here: http://mysqlserverteam.com/multi-table-trick/
Excellent, thank you for clarifying!
It is worth pointing out that point in time recovery uses the same binary logs, so in the absence of replication it is still important to make sure the backup is taken when no temporary tables are open. I believe that both MySQL Enterprise Backup and Xtrabackup understand how to 'wait' for temp tables to close (the server exposes this meta data). It is possible that a user-scripted backup around mysqldump may not do this though.