[Spamassassin Wiki] Update of "BetterDocumentation/SqlReadmeAwl" by KevinMcGrail

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Spamassassin Wiki" for change notification.

The following page has been changed by KevinMcGrail:
http://wiki.apache.org/spamassassin/BetterDocumentation/SqlReadmeAwl

------------------------------------------------------------------------------
  
  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:
  
- {{{`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,}}}
+ {{{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:
  
@@ -175, +175 @@

  
     -- 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
+