Latest Register Log In

+ Advanced Search

Archiving Comment Posts

Archiving Comment Posts
By
Mar 28, 2013 (Edited Mar 11, 2014)

WSN is highly scalable, but millions of long comment 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_comments table. Go to the operations tab, and use that to copy the table structure and data to yourprefix_archivedcomments.

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

UPDATE {PREFIX}links SET archived=1, threadclosed=1 WHERE lastcomment < 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 comments 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('commentstable', "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>




Description Improving database speed on a site with millions of posts.
Rating
Views 298 views. Averaging 0 views per day.
Similar Listings