# How to speed up Delete?

**URL:** <https://forums.percona.com/t/how-to-speed-up-delete/1114>\
**Category:** Other MySQL® Questions\
**Created:** [March 13, 2009, 8:21pm UTC](https://forums.percona.com/t/how-to-speed-up-delete/1114 "2009-03-13T20:21:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![hyelluas1](https://avatars.discourse-cdn.com/v4/letter/h/bb73d2/32.png) [@hyelluas1](https://forums.percona.com/u/hyelluas1)\
**Post date:** [March 13, 2009, 8:21pm UTC](https://forums.percona.com/t/how-to-speed-up-delete/1114/1 "2009-03-13T20:21:41Z")

</div>

Hello,

my database loads 120,000 rec in 1 hour,keeps 2 hours and rolls the oldest hour into hrly table.  
Then I have to DELETE that rollup hour.  
I was using partitions in comminity version,but our classic has no partitions and I need to get it working with “delete”.

I need to delete 120,000 out of 300,000 rec in less 1 min…  
The next step is even worse:  
I need to delete 2,5 mln rec out of 40 mln rec. eek: eek:  
Even if I do it by chanks - it took very long time, 12 min! to delete 2,000 rec from 20 mln rec table

I do have an index on the field for delete.

However I noticed that

delete from a where datex=‘2009-03-12 02:03:01’  
or delete from a where datex between t1 and t2  
( uses an index,correct?)

runs 4 times slowly then

delete from a where date(datex)=datex2009-03-12’  
( does not use an index)

I’m using MyISAM , 4G memory

max\_tmp\_tables=64  
tmp\_table\_size=32M  
max\_heap\_table\_size=32M  
table\_open\_cache=512  
table\_cache=2table\_cache

query\_cache\_size=64M  
query\_cache\_type=1  
query\_cache\_limit=4query\_cache\_limit

max\_connections=100  
key\_buffer=512M

myisam\_sort\_buffer\_size=250M

All the suggestions are greatly appreciated!

thank you.  
Helen

---

<div class="post-metadata">

**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\
**Post date:** [March 13, 2009, 11:43pm UTC](https://forums.percona.com/t/how-to-speed-up-delete/1114/2 "2009-03-13T23:43:06Z")

</div>

delete from a where datex between t1 and t2

should be the fastest. Make sure you have an index on `a`.

Also, given that you’re deleting a range, if you made the table InnoDB, and made `a` the primary key, the delete would occur in seconds at most. This is because InnoDB stores rows in primary key order – so deleting a range would be touching row physically next to each other on the disk, making the changes extremely quick. MyISAM doesn’t support this. If you decide to try InnoDB, investigate the setting innodb\_flush\_logs\_at\_trx\_commit=2 . It will make inserts and updates as fast as MyISAM. Of course, you’ll need to adjust innodb\_buffer\_pool\_size to a reasonable size.

---

<div class="post-metadata">

**Author:** ![hyelluas1](https://avatars.discourse-cdn.com/v4/letter/h/bb73d2/32.png) [@hyelluas1](https://forums.percona.com/u/hyelluas1)\
**Post date:** [March 14, 2009, 12:52pm UTC](https://forums.percona.com/t/how-to-speed-up-delete/1114/3 "2009-03-14T12:52:46Z")

</div>

Thank you,MarkRose!

I have MyISAM tables. I do have an index on a(datex) and my new tests showed that index speeded up.

but it is still too slow.

Any ideas to change db parameters?

thank you!  
Helen