[buro9]

When I've done the DOM JavaScript stuff I'll post it here. It may be a few weeks though, I'm still running the old javascript on my site, the new stuff I haven't had tested in many browsers (just Firefox on Ubuntu), so I've yet to even get it past IE on Windows And I'm going to be distracted and slow working for a fortnight as my girl is coming to stay for a couple of weeks... this takes a lower priority

As for the basics to make things better now:
In vaispy.php:
REPLACE:

$daysprune = 1;

WITH:

$daysprune = 0.04;

That will take one days worth of queries down to 1 hour worth of queries (57.1 minutes to be precise). So even fewer rows in the thread table locked.

In va_spy.js:
REPLACE:

var xmldelay = 7000;

WITH:

var xmldelay = 20 * 1000;

AND

REPLACE THE TWO INSTANCES OF:

setTimeout("getXML()", 5000);

WITH:

setTimeout("getXML()", xmldelay);

That changes the time between polling for updates from 7 seconds to 20. I prefer doing the math like that as it makes it obvious it's 20 x 1000 milliseconds = 20 seconds.

Changing that you will have taken each users average 10 queries per minute down to 3 queries per minute. Change it to 30 seconds and they'll be down to 2 queries per minute. Any time span higher than this will reduce the beauty and usability of the spy page too much for my liking. But quiet forums could easily get it to 1 minute per poll.

I also don't like * queries in SQL, so I changed in vaispy.php:

    $getthreads = $db->query_read("
        SELECT thread.*, post.pagetext AS preview, post.userid AS lastpuserid
        FROM ".TABLE_PREFIX."thread AS thread
        LEFT JOIN ".TABLE_PREFIX."deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
        LEFT JOIN ".TABLE_PREFIX."post AS post ON(post.postid = thread.lastpostid)
        WHERE open <> 10 AND thread.lastpostid > $lastpostid
        AND forumid NOT IN (0$blockforums)
        AND thread.visible = '1'
		AND post.visible = 1
        AND deletionlog.primaryid IS NULL
        $datecut
        ORDER BY lastpost DESC LIMIT 10");

TO:

    $getthreads = $db->query_read("
      SELECT thread.forumid,
             thread.firstpostid,
             thread.lastpost,
             thread.lastposter,
             thread.lastpostid,
             thread.replycount,
             thread.threadid,
             thread.title,
             thread.views,
             post.pagetext AS preview,
             post.userid AS lastpuserid
        FROM " . TABLE_PREFIX . "thread AS thread
             LEFT JOIN " . TABLE_PREFIX . "deletionlog AS deletionlog ON (thread.threadid = deletionlog.primaryid AND type = 'thread')
             LEFT JOIN " . TABLE_PREFIX . "post AS post ON (post.postid = thread.lastpostid)
       WHERE open <> 10
         $datecut
         AND forumid NOT IN (0$blockforums)
         AND thread.visible = '1'
         AND thread.lastpostid > $lastpostid
         AND post.visible = 1
         AND deletionlog.primaryid IS NULL
       ORDER BY thread.lastpost DESC LIMIT 10
    ");

The difference on that is neglible on performance, but I prefer to know what my queries return.