Page History

...

As with file-based AWL, the SQL version grows without bounds. I've forgotten where I found the original recommendation, but adding another column like this:

ALTER TABLE awl add column {{lastupdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,;}}

and adding a cron job to periodically trim entries that haven't been touched in a while:

...

helps keep it under control. This is especially important if you've made efforts to put the MySQL data store on a RAMdisk of some kind...

– Kris Deugau 2009-08-27

I've used a similar method based on count as well for mysql-based AWL storage! But this is definitely a good idea for AWL.

Suggest that you also add an index to the column like this for mysql:

ALTER TABLE awl ADD INDEX lastupdate(lastupdate);

And I also have an index on count and would recommend running 4 tiers of deletions via cron:

DELETE FROM awl WHERE lastupdate <= (now() - INTERVAL 15 day) and count < 5;
DELETE FROM awl WHERE lastupdate <= (now() - INTERVAL 30 day) and count < 10;
DELETE FROM awl WHERE lastupdate <= (now() - INTERVAL 60 day) and count < 20;
DELETE FROM awl WHERE lastupdate <= (now() - INTERVAL 120 day);

– Kevin A. McGrail 2009-08-27