November 27th, 2012, 10:05 PM
[MySQL - PHP] Deleting many rows in a large table
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 10:09 PM.
Tags for this Thread
Click Here to Expand Forum to Full Width
This is a CodeGuru survey question.