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.

[mute]

Does anyone know if this made its way into 3.5.0?

[lazee]

I used this one smooth and help my board alot with no problem ,but today when i notice that the thead didn't count when people view , i ran the cron job theadview in schedules and i got this error :
Database error in vBulletin 3.0.7:

Invalid SQL: INSERT INTO threadviewsaggregate SELECT threadid , COUNT(*) AS views FROM threadviews GROUP BY threadid
mysql error: Duplicate entry '40' for key 1

mysql error number: 1062

Date: Tuesday 28th of June 2005 08:09:36 PM
Script: http://xx.com/forum/admincp/cronadmi...ncron&cronid=2
Referer: http://xx.com/forum/admincp/index.php?

anyone got any idea why i got this and how to fix ,thank in advance . I used to run the threadview before and nerver got this error . before this error, i didn't restore db or install anything .

[kmike]

You should empty threadviewsaggregate table. It is cleared at the end of cron script run, but it didn't happen at some point for some reason. Theoretically, it can happen when you shutdown http server in the middle of cron job run.

[lazee]

Thank Kmike .it working after i empty the threadview.. . I think i did restart the apache that was cause the problem like you said .

[michaelbenson]

Erm, where exactly is this threadviews.php file?
I have tried looking around and i am using 3.0.9 has it since been removed?

[Andreas]

includes/cron