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.

put your codes in [ PHP ] tags please...

also, can you point a real MySQL doc where the example is explained and rated ?!... because i can see your point, as a mysql developper, but many others will discuss it...

[dwh]

I copied it from vb.com and on preview the php code displayed. I edited it.

On the mysql:
http://dev.mysql.com/doc/mysql/en/update.html

Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.priceWHERE items.id=month.id;The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.

Before MySQL 4.0.18, you need the UPDATE privilege for all tables used in a multiple-table UPDATE, even if they were not updated. As of MySQL 4.0.18, you need only the SELECT privilege for any columns that are read but not modified.

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

Currently, you cannot update a table and select from the same table in a subquery.

ok, no problem btw...

but i would usggest you to release it in the BETA forum instead, because it's in development, not really releaseable on production sites...

but maybe you can modify it to have a if statement depending on PHP version we have on our server...

[kmike]

Originally Posted by nexialys

but i would usggest you to release it in the BETA forum instead, because it's in development, not really releaseable on production sites...

but maybe you can modify it to have a if statement depending on PHP version we have on our server...

It's actually pretty much of production quality, I'm running this with small changes now. According to dwh's thread at vb.com, he has this hack applied and running, too.
This hack doesn't depend on PHP version, only on MySQL version - anything greater than 4.0 works.

My changes are:
1. This string has a typo, there should be a space after "FROM"

PHP Code:

$DB_site->query("DELETE FROM". TABLE_PREFIX ."threadviewsaggregate"); 


2. After applying hack, move this line immediately after the line with INSERT SQL statement:

PHP Code:

$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews"); 


Reason for this is explained here:
http://www.vbulletin.com/forum/showp...4&postcount=16

[Boofo]

Originally Posted by kmike

It's actually pretty much of production quality, I'm running this with small changes now. According to dwh's thread at vb.com, he has this hack applied and running, too.
This hack doesn't depend on PHP version, only on MySQL version - anything greater than 4.0 works.

My changes are:
1. This string has a typo, there should be a space after "FROM"

PHP Code:

$DB_site->query("DELETE FROM". TABLE_PREFIX ."threadviewsaggregate"); 


2. After applying hack, move this line immediately after the line with INSERT SQL statement:

PHP Code:

$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews"); 


Reason for this is explained here:
http://www.vbulletin.com/forum/showp...4&postcount=16

So what should the full code look like then?

[kmike]

Originally Posted by Boofo

So what should the full code look like then?

I see it's easier to paste whole file, just without header/footer:

PHP 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); 


[Erwin]

I encouraged dwh to post here. It's fine to be in this forum, no need to be in beta considering it's working on some large sites.

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

[dwh]

Originally Posted by Erwin

I encouraged dwh to post here. It's fine to be in this forum, no need to be in beta considering it's working on some large sites.

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

14 million posts???? No ****!
You deserve a beer.
Can you PM me your url? Would love to take a look.

[Brad]

Erwin's forum is very large

I have this running on my home box with no problems, probbly use this on my production site soon to.