View source for Modlog Pruning

XMB does not have a built-in pruning feature for the new modlog data feature. Modlogs are those messages like "thread closed", "thread moved" etc. The lack of a pruning option is by design. Originally, it was thought that these data should be preserved forever and nobody needed an easy way to delete them.

However, after some time spent behind the wheel of version 1.9.11, the XMB staff decided that the modlog data do become unimportant when they are very old. At XMB, we adopted a policy of pruning our own modlogs when they are over 6 months old.

As you may already know, Super Administrators can run any SQL code from the Admin Panel. This code will display the number of modlog items that are older than 6 months:

<code>SELECT COUNT(*) FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000</code>

UNIX_TIMESTAMP() is the MySQL way of saying "fetch the current time in XMB format", and 15768000 is the number of seconds in half of a non-leap year.

xmbforum2.com operates a medium size forum, and it is common to have many thousands of modlog items that are over 6 months old. We don't want to delete them all at once due to performance concerns. Any time a DELETE query runs against a MyISAM type table, the entire table is locked until the query finishes running. So to keep things sane, the LIMIT clause is added:

<code>DELETE FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000 LIMIT 1000</code>

After running this query repeatedly (and giving the server a moment to recover in between runs) we are left with only the newest items. And finally:

<code>OPTIMIZE TABLE $table_logs</code>

This helps to clean up the unused parts of the table and defragment the file it is stored in.