Fixed Inefficient update of thread view counter

[digitalpoint]

Every hour a cron job kicks off that spins through every thread viewed at least once in the last hour to update it's view counter.

For us, it makes our DB server queries per second chart look like this:

The spikes are the counters being updated (1 query per thread viewed in the last hour). When you have 30,000+ unique threads being viewed every hour, it starts to be a little silly with 30,000+ SQL write queries going on.

You can make it much more efficient by JOINing against a temporary table and updating based on the JOIN and rewriting the XenForo_Model_Thread::updateThreadViews() method to use a query like this:

UPDATE xf_thread
	INNER JOIN (
		SELECT thread_id, COUNT(*) AS total
		FROM xf_thread_view
		GROUP BY thread_id
	) AS xf_tv ON (xf_tv.thread_id = xf_thread.thread_id)
SET xf_thread.view_count = xf_thread.view_count + xf_tv.total

I just tested it and rather than doing 30,000 SQL UPDATEs every hour, it was able to update all threads that needed it with a single query that took 0.06 seconds to run (against a DB with ~2.6M threads).

 

[digitalpoint]

Side note... it probably would be more efficient for xf_thread_view to have 2 columns (thread_id and count) and just do an INSERT ON DUPLICATE KEY UPDATE. On average threads are going to be viewed more than once per hour, so creating 100 records with the thread ID for 100 views doesn't seem terribly efficient vs. thread ID + the number 100. And as a bonus you wouldn't even have to create the temp table that summarizes records like shown above.

 

[Mike]

Trying out the sub query method now. No schema change to that table yet. I think it was done that way to allow DELAYED inserts so we didn't have to worry about potentially slowing down the load to write the record.

 

[digitalpoint]

Is it actually an issue? You could relegate it to some sort of shutdown query or something if so?