Consider this statement (yeah yeah, i know :)
mysql> delete from t1 order by rand();Query OK, 78130 rows affected (2.61 sec)
t1 has no indexes and is an int field with numbers from 1 to 78130. However, this will cause the slave to re-read entire table for each row deleted! Here it's still running, causing 100% cpu usage:
---TRANSACTION 0 1799, ACTIVE 2390 sec, OS thread id 3672 fetching rows mysql tables in use 1, locked 1 153 lock struct(s), heap size 30704, 78281 row lock(s), undo log entries 35423Number of rows inserted 78130, updated 0, deleted 35423, read 1076560253 0.00 inserts/s, 0.00 updates/s, 17.58 deletes/s, 367099.91 reads/s
Over a billion row reads 40 minutes later and it's not even half done yet.For a large table this could take weeks or years to complete. It would be nice if there was a way to prevent this situation from happening.
PlanetMySQL Voting: Vote UP / Vote DOWN