Deleting old Snort and BASE event data from MySQL

The following SQL that I recently cooked up thoroughly clears out the data from events that are more than 28 days old, then reoptimises the tables. It takes some time to complete so it is probably best scheduled as a regular night time cron job.

The DELETE FROM ... USING syntax is quite appealing and powerful...

DELETE FROM event WHERE timestamp < DATE_SUB(NOW(),INTERVAL 28 DAY);

DELETE FROM data    USING data    LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM iphdr   USING iphdr   LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM icmphdr USING icmphdr LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM tcphdr  USING tcphdr  LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM udphdr  USING udphdr  LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM opt     USING opt     LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;

DELETE FROM acid_event USING acid_event LEFT OUTER JOIN event USING (sid,cid) WHERE event.sid IS NULL;
DELETE FROM ag USING acid_ag_alert AS ag LEFT OUTER JOIN event AS e ON ag.ag_sid=e.sid AND ag.ag_cid=e.cid WHERE e.sid IS NULL;

OPTIMIZE TABLE event, data, iphdr, icmphdr, tcphdr, udphdr, opt, acid_event, acid_ag_alert;