Given that I have a table with about 9 million rows in it, I want to
delete 480,000 of them matching a specific criteria. To do this in
the least intrusive fashion, I have this loop running:
sleep 1 second
DELETE LOW_PRIORITY FROM xxxx WHERE xxxx LIMIT 1000
repeat unless affected rows < 1000
Now, the question is about LOW_PRIORITY. The docs say that
If you specify the keyword LOW_PRIORITY, execution of the DELETE is
delayed until no other clients are reading from the table.
Does this also delay the execution of the DELETE while other clients
are inserting into this table? That's pretty much all that the other
clients are doing, and it seems from my experiments that this is the
case. However, without the sleep 1 second in the loop, the other
clients seem to backlog more (which is probably an artifact of the
thread scheduling.) So for now I'm sticking with the sleep... this
method is still gonna take a long time to delete the data at an
average of 15 to 20 seconds per 1000 rows deleted!
Also, does "EXPLAIN SELECT * FROM xxx WHERE ..." give the same
"explanation" as MySQL would use for the same "DELETE FROM xxx WHERE
..." or does DELETE not optimize the same?
This is running 3.22.14-gamma, for sun-solaris2.6 on sparc.
Any comments on the methodology and assumptions would be appreciated.
