Remove 1 Database Query Per Page

Remove 1 Database Query Per Page

Intro:
The idea is, lets remove 1 query from each page load, if the person is just refreshing the page. At first this may sound not important or just plain stupid, but on "larger" forums, cutting 1 query is always welcomed I know.

Description:
Basically all this does, is check the current Session Location from the DB against the current WOL path; the Session is already provided by vbulletin, so no extra query there.

All we do, is simply check the Location from the Database against the current WOL, if they don't match then the query is allowed to update the database; if they are the same - it is simply ignored.

Affects:
The only things this will do, is prevent the updating of the "lastactivity", and "location" if a person keeps refreshing the page, IMO that's not really a big deal, vbulletin takes care of deleting and remaking new sessions when they expire anyway. As long as your members are browsing around and such, their lastactivity, location will be updated as per usual.

Updates:
Version 1.1 - Scripts whosonline, misc are allowed to always update.
Version 1.0 - Release

Instructions:
1. [ open includes/sessions.php ]
2. [ go to about line 354, or look for $bypass = intval(SESSION_BYPASS); ]
3. [ replace this block of code ]

PHP Code:

    $DB_site->shutdown_query("
        UPDATE " . TABLE_PREFIX . "session
        SET useragent = '" . addslashes(USER_AGENT) . "', lastactivity = " . TIMENOW . $location . ", styleid = $styleid" . iif(VB_AREA !== 'Upgrade', ", bypass = $bypass") . "
        ###REPLACE###
        WHERE sessionhash = '" . addslashes($session['sessionhash']) . "'"
        , 'sessionupdate'
); 


4. [ with this block of code]

PHP Code:

    /**
     * Update Location On Page Change
     *
     * @author Trigunflame
     * @version 1.1
     * @copyright Dusty Burns 2005-2006
     */

    // Check Script
    if ($session['location'] != WOLPATH || THIS_SCRIPT == 'misc' || THIS_SCRIPT == 'online')
    {
        $DB_site->shutdown_query("
            UPDATE " . TABLE_PREFIX . "session
            SET useragent = '" . addslashes(USER_AGENT) . "', lastactivity = " . TIMENOW . $location . ", styleid = $styleid" . iif(VB_AREA !== 'Upgrade', ", bypass = $bypass") . "
             ###REPLACE###
             WHERE sessionhash = '" . addslashes($session['sessionhash']) . "'"
            , 'sessionupdate'
        );
    }

    /**
     * End Update Locations On Page Change
     */ 


5. [ save file ]

[neocorteqz]

Originally Posted by Trigunflame

*wonders how many people have actually used this*

Surprised not that many... oO

I just found this thread.

[GlitterKill]

After installing this hack I am now getting emails like this... 5 so far today:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Friday 20th of May 2005 03:56:02 PM
Script: http://forums.pimprig.com/cron.php?&rand=50303
Referer: http://forums.pimprig.com/showthread.php?t=44214
Username: Peng Lord
IP Address: 66.167.218.162

and another:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Friday 20th of May 2005 02:56:00 PM
Script: http://forums.pimprig.com/cron.php?&rand=325958
Referer: http://forums.pimprig.com/member.php?u=2606
Username: SpikeShot
IP Address: 68.52.66.139

My dedicated server runs PHP 4.3.11 and MySQL 4.0.24. Any ideas?

[neocorteqz]

I do believe your problem is here

INSERTINTOthreadviewsaggregate

Do you have another hack installed which uses the same file?

[Trigunflame]

Originally Posted by GlitterKill

After installing this hack I am now getting emails like this... 5 so far today:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Friday 20th of May 2005 03:56:02 PM
Script: http://forums.pimprig.com/cron.php?&rand=50303
Referer: http://forums.pimprig.com/showthread.php?t=44214
Username: Peng Lord
IP Address: 66.167.218.162

and another:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Friday 20th of May 2005 02:56:00 PM
Script: http://forums.pimprig.com/cron.php?&rand=325958
Referer: http://forums.pimprig.com/member.php?u=2606
Username: SpikeShot
IP Address: 68.52.66.139

My dedicated server runs PHP 4.3.11 and MySQL 4.0.24. Any ideas?

Don't know what you edited, but it doesn't have anything to do with this hack.. as you are not dealing with anything involving "Inserting" into "Threadviews", you just messed up somewhere.

[Marco van Herwaarden]

The errors are coming from some Scheduled Task, not from this hack.

[GlitterKill]

Sorry this was meant to go into the authors other optimization thread. Sorry.

[David Bott]

Installed on AVS Forum.

[Thug]

installed thanx