Optimizing complex updates/deletes for MySQL replication

In a MySQL master-slave environment, when you do an UPDATE or DELETE with a complex WHERE clause, the complex query gets executed on every slave server, which is horrible if your slaves are already serving a high volume of read requests. But this can be avoided.

MySQL's replication binlogs only replicate queries that modify data. So you can make the slaves do less work by running a SELECT first with the complex WHERE clause, then a UPDATE or DELETE with a simple WHERE clause. By breaking it up into 2 queries like this, only the simple query gets passed down and executed on the slave servers.

An example of where this might be useful is deleting a set of records that relate to another DELETE operation, and where there is no useful index that can be used to locate the related records. For example email aliases.... If a customer deletes user@example.com we need to also update/delete any aliases that point to user@example.com. If you store alias destinations in an unindexed comma delimited column, a LIKE statement or other complex string parsing statement must used in the WHERE clause to update/delete these related records. In this scenario, it would be much better to first run a SELECT query to find all alias records that you need to update/delete, and then run each update/delete query with a simple WHERE clause that references an indexed column.