# How to speed up DELETE with innodb table

**URL:** <https://forums.percona.com/t/how-to-speed-up-delete-with-innodb-table/1007>\
**Category:** Other MySQL® Questions\
**Created:** [November 19, 2008, 6:06am UTC](https://forums.percona.com/t/how-to-speed-up-delete-with-innodb-table/1007 "2008-11-19T06:06:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TommyDIY](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@TommyDIY](https://forums.percona.com/u/TommyDIY)\
**Post date:** [November 19, 2008, 6:06am UTC](https://forums.percona.com/t/how-to-speed-up-delete-with-innodb-table/1007/1 "2008-11-19T06:06:26Z")

</div>

Hi

We have a logging table that contains a months worth of data (about 10Million records) and we run a clean up script everyday that deletes a days worth of records (about 250k) so that we only have the last 31 days worth of information retained. This is taking hours to run!!

OS: Ubuntu 32bit version  
MySQL version: 5.0.51a-3ubuntu5.1-log  
RAM: 4GB

Some (hopefully) useful MySQL info has been included in the info.txt along with output from top, vmstat and iostat

We have tried various DELETE querys  
DELETE FROM log WHERE DATEDIFF(current\_date(),dt)\>31;

and even added a AUTO INC index called myid to see if that speeds up things  
SELECT @l\_id:=MIN(myid) FROM log;  
SELECT @m\_id:=MAX(myid) FROM log WHERE DATEDIFF(current\_date(),dt)\>31;  
DELETE FROM log WHERE myid between @l\_id and @m\_id;

A select works very fast  
mysql\> SELECT count(_) FROM cpelog WHERE myid between @l\_id and @m\_id;  
±---------+  
| count(_) |  
±---------+  
| 259200 |  
±---------+  
1 row in set (0.19 sec)

What can we do to speed up the delete?

Do I have too many indexes as I see that the index size is is greater that the table data size and approaching the 4GB physcial limit to my RAM?I see that that my SWAP does not seems to be used though so sure if that is the problem.

Any help most appreciated.  
Cheers  
Tom

---

<div class="post-metadata">

**Author:** ![TommyDIY](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@TommyDIY](https://forums.percona.com/u/TommyDIY)\
**Post date:** [November 20, 2008, 8:50am UTC](https://forums.percona.com/t/how-to-speed-up-delete-with-innodb-table/1007/2 "2008-11-20T08:50:18Z")

</div>

Out of curiosity and as a half baked idea towards a solution I also tried the following.

Copy all the the data I want to keep into an identical table and once that is complete drop the old log table and rename the new one

mysql\> INSERT INTO log\_temp SELECT \* from log where DATE\_SUB(CURDATE(), INTERVAL 31 DAY) \<= dt;  
Query OK, 9740800 rows affected (1 hour 8 min 39.67 sec)  
Records: 9740800 Duplicates: 0 Warnings: 0

Obviously this is not the solution either as it takes over a hour to run. But it is amazing that it takes nearly as long to copy 9,740,800 rows to a new table as it does to delete 250,000.

My brain is melting here…

---

<div class="post-metadata">

**Author:** ![TommyDIY](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@TommyDIY](https://forums.percona.com/u/TommyDIY)\
**Post date:** [November 24, 2008, 8:36am UTC](https://forums.percona.com/t/how-to-speed-up-delete-with-innodb-table/1007/3 "2008-11-24T08:36:02Z")

</div>

Well I dropped 3 of the indexes on this table only leaving the index on the dt column and got the desired result I was looking for.

mysql\> delete FROM log WHERE dt \< DATE\_SUB(CURDATE(), INTERVAL 31 DAY);  
Query OK, 259200 rows affected (6.45 sec)

It would seem that I will have to revisit the usefulness of indexes when it comes to the various queries that are run on this table. As it would seem that 4 is 2 many for the table of this size.

Hope this info is useful to some other sucker

---

<div class="post-metadata">

**Author:** ![bstjean](https://avatars.discourse-cdn.com/v4/letter/b/f17d59/32.png) [@bstjean](https://forums.percona.com/u/bstjean)\
**Post date:** [November 25, 2008, 10:21am UTC](https://forums.percona.com/t/how-to-speed-up-delete-with-innodb-table/1007/4 "2008-11-25T10:21:17Z")

</div>

Have you tried deleting in “chunks”, not all at once, by repeating your delete statement as in :

DELETE FROM log WHERE DATEDIFF(current\_date(),dt)\>31 LIMIT 5000

_Sometimes_ this gives better results. Besides, you ensure your application is somehow responsive all the time (as long as the number of records is not too big)…

One other thing, you might be better with a constant instead of a function call in your WHERE clause.

So something like (assuming we’re ‘2008-11-25’:

DELETE FROM log WHERE dt \< ‘2008-10-25’

can use an index on column dt