[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.

Fastest way is to put the dates in the SQL query..

Code:

---

Delete from [table] where [daterow] between [date1] and [date2]

---

and call it once...

something else to assist in the speed is to create an index on the Daterow, this will assist the Query to locate all relevant records..

Yea... I have a timestamp in the table.
And the column is indexed too.
However, it is still taking a long time to delete the records.

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...

To delete like 100k records from 1million records will take more than 5minutes.

Maybe I can look up on TableLock.

I don't have any foreign keys or linked tables. This table is independent.

There couldn't be any simpler way other than "DELETE FROM table_name WHERE timeinlong BETWEEN 'fromdate' AND 'todate'".

Still trying to come up with a better way.

Thanks! let me know if you can think of others.

There is lots of talk on some sites that Updates and inserts are faster than deletes in many SQL database systems..

try this code (as is for now, because it can be destructive to Data) and see how long this takes to run.. If it's shorter i'll show you how to change it to work in place of a delete..

Code:

---

select *  into #tmptable from [table]
 WHERE not([daterow] BETWEEN '[date1]' AND '[date2]')
truncate table #tmptable
select *  into #tmptable from [table]

---

Just one thing... Does the table have a Auto numbering field (Identity Specification = yes)??

Quote:

---

Originally Posted by tiestoycc

To delete like 100k records from 1million records will take more than 5minutes.

---

That is absolutely ridiculous. Even for an old server with very few resources it should take seconds on a non-indexed table and nanoseconds on an indexed table.

What is your table structure? Any large blobs in these?

This is what I did:

mysql> delete from user_table where username='john';
Query OK, 203601 rows affected (6 min 6.88 sec)

and the username column is indexed as well.

We have 63 columns in the table with 5 indexes. No, there are no large blobs in the table.

Quote:

---

Originally Posted by GremlinSA

There is lots of talk on some sites that Updates and inserts are faster than deletes in many SQL database systems..

try this code (as is for now, because it can be destructive to Data) and see how long this takes to run.. If it's shorter i'll show you how to change it to work in place of a delete..

Code:

---

select *  into #tmptable from [table]
 WHERE not([daterow] BETWEEN '[date1]' AND '[date2]')
truncate table #tmptable
select *  into #tmptable from [table]

---

Just one thing... Does the table have a Auto numbering field (Identity Specification = yes)??

---

Yea, Updates, Inserts and Selects are a lot faster than deletes. I can select hundreds of thousands of records in less than a second. But when it comes to deleting, it takes too long.

No, there is no auto-increment field.