List:General Discussion« Previous MessageNext Message »
From:Vivek Khera Date:June 15 1999 10:03pm
Subject:question on DELETE LOW_PRIORITY optimization
View as plain text  
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.
Thread
question on DELETE LOW_PRIORITY optimizationVivek Khera16 Jun
  • question on DELETE LOW_PRIORITY optimizationMichael Widenius25 Jun