How to speed up Delete?

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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