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]

This does look very promising, has jelsoft thought about integrating it into the product?

[dwh]

Yes they are.

I haven't had a chance to develop it yet but using the same principle Ibelieve there are a couple of other places this concept can be used.

[mute]

Thats great news. Now if I can only work up the nerve to upgrade to 3.1.0 when it comes out

[deathemperor]

Originally Posted by Erwin

I may add this to my forum with over 14mil posts... I am always wary of database corruption.

WTF ! YEAH I mean the number, 14mil posts is really far off my hands ! OMG !

[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:

[dwh]

Is the lack of spaces only coming from your post or is it in your code as well? And what version of mysql do you have installed?

[T3MEDIA]

good questions.

[red_baron2000]

will certainly give it a go ..

[detalhe]

Originally Posted by dwh

Is the lack of spaces only coming from your post or is it in your code as well? And what version of mysql do you have installed?

Now that you mention it, I think you should correct your code above, I think that lack of spaces comes from it

PHP Code:

$DB_site->query("INSERTINTO".TABLE_PREFIX ."threadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM " . 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"); 


Will try it

[Dean C]

Indeed you need to space your query out - that's why it's not working