Deletion causes problems!

[Jaelyn]

hello guys,
here is the issue!
I have a transaction table. Records are added regularly. When I decided to purge the 500,000 oldest. I ran this simple command:

DELETE FROM am_log WHERE id<500000

It ran for 40 minutes, caused timeouts for other operations calling the database. When it was done, the table was no longer functional as am_log.myi was corrupted. After a long rebuild, it was back.

how best to approach keeping this table a managable size and in good shape? I could run a cron every night and knock off 50K records or so. If I do one of those, how often do I need to optimize the table? How long will the optimize process take? What happens when the table is called by some function while it is being optimized? -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[Jan]

you could try deleting in smaller chunks. If you're getting a lot of records you probably do want to cron somethig to delete records you don't want.
Or instead of sticking tons of records into one table, have a script to create a new table for each day so you only get a moderate amount of records in a single table each day.

Optimizing the table depends on the length, it'll help a lot after you delete a lot of records for certain. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --