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!