Cleanup jobs have insufficient performance on MySql

Both the build expiry and the "Orphaned Result Deletion Job" (a scheduled job
that runs every 120 seconds) perform very poorly on our large MySql Database. We have identified the culprit queries and managed to optimize those for mysql (See patch: speed_up_build_expiry_mysql.patch).

Without the patch, queries like this one:

delete from VARIABLE_SUBSTITUTION where BUILDRESULTSUMMARY_ID in (
       select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY
       where BUILDRESULTSUMMARY.BUILD_KEY = "BAM-1");

run over 120 Seconds, as they do a full table scan on VARIABLE_SUBSTITUTION
(which is really large). To make things worse, the query locks
BUILTRESULTSUMMARY, so that queries that try to write to it run into the lock
timeout. With some optimizations to the database (anything that is suggested
here, and more), we were able to mitigate this a bit, but the issue still persists.

The patch uses MySql specific "delete from join" syntax, but only if the database is MySql. Using this, the equivalent delete queries execute in under a second (as they are able to use the indices).

Also see this question I asked on dba.stackexchange.com.