Improve performance on thread view updating

Per Doc Erwin:
http://www.vbulletin.com/forum/showp...8&postcount=18
I'm releasing this here.

I have a suggestion to improve performance although it is for mysql 4.04 and up (I assume at some point a future version of vb will require it).

Requirements: MySQL 4.04

If you have a large forum, run mysql 4.04 and up and want to try this, please report back on your results.

The way updating threads views works right now, (even though putting it in cron improved things,) you are still going through many queries to the thread table, potentially locking it up while updating views.The current code from threadviews.php is:

PHP Code:

PHP Code:

$threads = $DB_site->query("SELECT threadid , COUNT(*) AS views FROM " . TABLE_PREFIX . "threadviews GROUP BY threadid");
 
while ($thread = $DB_site->fetch_array($threads))
{
$DB_site->query(
"UPDATE " . TABLE_PREFIX . "thread
SET views = views + " . intval($thread['views']) . "
WHERE threadid = " . intval($thread['threadid'])
);
} 


With 4.04 you can do a multitable update.
FIRST, create the table ONCE ONLY
PHP Code:

PHP Code:

CREATE TABLE `threadviewsaggregate` (
`threadid` int(10) NOT NULL default '0',
`views` int(10) NOT NULL default '0',
PRIMARY KEY (`threadid`)
) TYPE=MyISAM; 


and in the threadviews.php code change it to

PHP Code:

PHP Code:

$DB_site->query("INSERT INTO ".TABLE_PREFIX ."threadviewsaggregate SELECT threadid , COUNT(*) AS views FROM " . TABLE_PREFIX . "threadviews GROUP BY threadid");
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews"); 
 
$DB_site->query(
"UPDATE " . TABLE_PREFIX . "thread,". TABLE_PREFIX ."threadviewsaggregate 
SET ". TABLE_PREFIX ."thread.views = ". TABLE_PREFIX ."thread.views + ".TABLE_PREFIX."threadviewsaggregate.views
WHERE ". TABLE_PREFIX ."thread.threadid = ". TABLE_PREFIX ."threadviewsaggregate.threadid"); 
 
$DB_site->query("DELETE FROM ". TABLE_PREFIX ."threadviewsaggregate"); 


That will reduce the number of queries by however many threads get updated in the cron interval. Additionally, you might be able to skip creating the extra table using subqueries. I haven't gotten around to writing that code yet. If someone wants to add that,mention it in this thread and I'll update the code.

[Marco van Herwaarden]

To run a query in your ACP, you must add your userid to the canrunquery variable in your config.php

[dsboyce8624]

And this is the correct code?

Originally Posted by tp214

nevermind!

this is the correct code...

error_reporting(E_ALL & ~E_NOTICE); 

if (!is_object($DB_site)) 
{ 
        exit; 
} 

$DB_site->query("INSERT INTO ". TABLE_PREFIX ."threadviewsaggregate SELECT threadid , COUNT(*) AS views FROM " . TABLE_PREFIX . "threadviews GROUP BY threadid"); 
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews"); 

$DB_site->query( 
        "UPDATE " . TABLE_PREFIX . "thread,". TABLE_PREFIX ."threadviewsaggregate 
        SET ". TABLE_PREFIX ."thread.views = ". TABLE_PREFIX ."thread.views + ".TABLE_PREFIX."threadviewsaggregate.views 
        WHERE ". TABLE_PREFIX ."thread.threadid = ". TABLE_PREFIX ."threadviewsaggregate.threadid"); 
$DB_site->query("DELETE FROM ". TABLE_PREFIX ."threadviewsaggregate"); 

log_cron_action('Thread Views Updated', $nextitem);

[dsboyce8624]

Okay, I put that code in and turned on logging. It says it ran fine.

Thanks for the help.

[GlitterKill]

Originally Posted by detalhe

Installed this and got this error after sometime:

PHP Code:

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(*)ASviews
 
mysql error number: 1064 


Will uninstall :disappointed:

I have used the most recent posted code that is supposed to be good but I get this type of error every hour:

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: Saturday 21st of May 2005 10:56:17 AM
Script: http://forums.pimprig.com/cron.php?&rand=634959
Referer: http://gallery.pimprig.com/showphoto...o=2553&cat=501
Username: Unregistered
IP Address: 86.133.1.23

[Marco van Herwaarden]

Insert spaces where needed. The code in this thread is somehow missing some spaces.

Edit: Edited the original code now with spaces inserted.

[kmike]

Took long enough...
But not all occurences are updated:

query("SELECTthreadid ,

[Marco van Herwaarden]

Originally Posted by kmike

Took long enough...

There was no reason at all for that remark, especially since the corrections are already posted in this thread (and it is not a 50 page thread).

Originally Posted by kmike

But not all occurences are updated:

Thanks fixed.

[kmike]

Sorry, I thought you're author of this hack but I see my mistake now.

[David Bott]

Installed on AVS Forum.

[WotC_Mel]

I see this has been installed successfully a few places. Anyone willing to comment on performance improvements they've notice?

-Mel