Re: So...no way to age records? I.e. remove...

[Beat Vontobel]

Hi Michael,

A simple solution would be to switch tables every year. Let your application store the data to a table named something like archive_current, then every year do an ALTER TABLE ... RENAME ... to change it's name to archive_YYYY and CREATE a new archive_current. You'll end up with tables archive_2003, archive_2004, archive_2005... Then just drop the oldest table. If you have to do the switch in an atomic way then you'd have to come up with a more clever sequence of statements, possibly using INSERT INTO ... SELECT ... to move your data to the archive and use a table that supports DELETE for the current year or some similar approach.

You can still access all the tables together with a UNION VIEW (see however my recent blog post at http://www.futhark.ch/mysql/137.html - ALGORITHM=MERGE is not yet allowed for such VIEWs, this could result in performance/memory usage issues).

Chers,
Beat

Beat Vontobel
http://www.futhark.ch/mysql



Edited 1 time(s). Last edit at 03/02/2006 05:41PM by Beat Vontobel.