Archiving Post Posts

WSN is highly scalable, but millions of long posts on a very popular website can slow down database queries on that table severely and increase hardware requirements. This mainly applies to discussion forums (WSN Forum), of course. Post searches end up taking interminably long and putting pressure on the server.

One solution is to archive old posts in threads which haven't had a reply in over a year. First, make sure you're running WSN 8.0.0 Alpha 6 or later. Then, go into phpmyadmin (in your web hosting control panel) and click on the yourprefix_posts table. Go to the operations tab, and use that to copy the table structure and data to yourprefix_archivedposts.

Then run this SQL query at Admin -> Miscellaneous -> Advanced Options:

UPDATE {PREFIX}links SET archived=1, threadclosed=1 WHERE lastpost < UNIX_TIMESTAMP()-86400*365;

You can change 365 to a desired number of days.

Finally, you'll need to run a utility to delete posts from the regular posts table. Save the following as a 1postdeleter.php file in your WSN folder:

<?php
require 'start.php';

if (!$start) $start = 0;
$perpage = 50;

$q = $db->select('id', 'linkstable', 'archived=1', "ORDER BY id ASC", "LIMIT $start,$perpage");

$n = $db->numrows($q);

if (!$n) die("Complete");

for($x=0;$x<$n;$x++)

{

$lid = $db->rowitem($q);

if ($lid) $db->delete('poststable', "linkid = $lid");

}

$start += $perpage;

echo "Continuing from $start... <meta http-equiv='refresh' content='5;url=1postdeleter.php?start=$start'>";

Now visit that file in your web browser and leave it running for a few hours untli it says complete. At this point, your site will be running much more efficiently and all posts not replied to for a year are locked. You might want to put a note in the view thread template about being archived due to age: <IF {LINKARCHIVED}><div class="alert">This thread has been archived due to age, you will need to start a new topic if you want to continue the discussion.</div></IF>