As the title suggest, I am using MySQL as my database and I would like to delete many rows (let's say 100k) in a large table (a few millions records). Users will select a date from when to when and choose to delete the records. The PHP script will then execute the query.
I found 2 ways to do it, one is to use a script to delete like 1000 or 10000 rows per second or so. I am using PHP for the scripting part.
Another is by renaming the table, then create a new table. After that, I delete the rows in the old table and then I transfer the records from the new table to old table.
But those 2 ways are still going to take a very long time. Is there any other solutions or ways that can be faster without using too much of the CPU?
If it can be done within 10 seconds, then that would be the best.
Any help would be much appreciated.
Last edited by tiestoycc; November 27th, 2012 at 09:09 PM.
Re: [MySQL - PHP] Deleting many rows in a large table
well how long does it take??
I know SQL server much better than MySQL, so the following might not exactly work, but look them up..
TableLock vs Rowlock :locking the table(rather than row) will prevent MySQL from trying to update the indexing after every row delete. (index's will be updated after all deletes are completed)
Foreign Key's : If the table has foreign keys, this could also slow down the delete ,as it also has to locate the Matching records in other tables. (Either ensure the Keys are indexed or remove the foreign keys before the delete and rebuild after the delete.)
Linked Tables : Are there tables that are dependent on this one.. A delete from this table will cause a (possibly slower non indexed assisted) delete in any dependent tables.
right now cant think of any others but there are a few more...