How to speed up Delete?


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





All the suggestions are greatly appreciated!

thank you.

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.

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!