Bug Optimising a query

[benFF]

Been getting a lot of hangs on the website for the last few months - so I've been collecting data in the slowqueries log.

A few crop up, however nothing really serious except this one:

# Query_time: 61.015625  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 139036
SET timestamp=1331681918;
SELECT
			entry.contentid,
			entry.varname,
			entry.dateline,
			user.userid,
			user.username,
			user.usergroupid,
			user.infractiongroupid,
			user.displaygroupid
		FROM vb_dbtech_thanks_entry AS entry
		LEFT JOIN vb_post AS post ON(post.postid = entry.contentid)
		LEFT JOIN vb_user AS user ON(user.userid = entry.userid)
		WHERE post.visible = 1
			AND post.userid NOT IN (6984,6845,9983,9986,42080) 
			AND post.threadid = '295450'
			AND entry.contenttype = 'post';

It's in the log very frequently with over 60 seconds of processing time!

Is there anything that can be done to help with this?

 

[Fillip H.]

Is threadid 295450 very big?

If you try to manually re-run that query in phpMyAdmin, does it always take ~60 seconds to process?

 

[benFF]

Sorry, I should have elaborated - that was just an example, the thread id is different each time (thread 295450 has only 2 posts!)

And no, it doesn't always take ages - but nothing else is cropping up in the logs.

(I appreciate there might be nothing that can be done - I can look into better mysql optimisation elsewhere, just wanted to check out here first )

 

[Fillip H.]

That's weird, queries popping up there are usually always slow...

Do they show up there frequently? Is there some form of pattern to the date / time (like always at 2am, etc)?


Don't get me wrong, I'll keep this issue open and definitely investigate ways to make the query faster, but if we found out it was due to a cron job or an automated backup or something then that would be a stopgap at least

 

[benFF]

Nah - the timing is totally random, nothing special going on at the time (unfortunately).

 

[Home Alone]

Is there an update on this? I'm experiencing the same slow queries. Here are some examples:

# Query_time: 10.681554 Lock_time: 0.000091 Rows_sent: 0 Rows_examined: 1414358
SET timestamp=1346876819;
SELECT
entry.contentid,
entry.varname,
entry.dateline,
user.userid,
user.username,
user.usergroupid,
user.infractiongroupid,
user.displaygroupid
FROM dbtech_thanks_entry AS entry
LEFT JOIN post AS post ON(post.postid = entry.contentid)
LEFT JOIN user AS user ON(user.userid = entry.userid)
WHERE post.threadid = '20955'
AND post.visible = 1
AND entry.contenttype = 'post';


Query_time: 22.903402 Lock_time: 0.000082 Rows_sent: 22 Rows_examined: 1413141
SET timestamp=1346875338;
SELECT
entry.contentid,
entry.varname,
entry.dateline,
user.userid,
user.username,
user.usergroupid,
user.infractiongroupid,
user.displaygroupid
FROM dbtech_thanks_entry AS entry
LEFT JOIN post AS post ON(post.postid = entry.contentid)
LEFT JOIN user AS user ON(user.userid = entry.userid)
WHERE post.visible = 1

AND post.threadid = '429929'
AND entry.contenttype = 'post';


Query_time: 22.304097 Lock_time: 0.000063 Rows_sent: 11 Rows_examined: 1413130
SET timestamp=1346875337;
SELECT
entry.contentid,
entry.varname,
entry.dateline,
user.userid,
user.username,
user.usergroupid,
user.infractiongroupid,
user.displaygroupid
FROM dbtech_thanks_entry AS entry
LEFT JOIN post AS post ON(post.postid = entry.contentid)
LEFT JOIN user AS user ON(user.userid = entry.userid)
WHERE post.visible = 1

AND post.threadid = '429904'
AND entry.contenttype = 'post';

 

[Fillip H.]

Please re-download and re-upload /dbtech/thanks/hooks/newreply_form_reviewbit.php and /dbtech/thanks/hooks/printthread_post.php and let me know if that works better for you.

Anecdotal tests here on DBTech show a significant improvement, although our database is not as large as yours.

If you wish to test it yourself in phpMyAdmin, run the following query and tell me its execution time:

SELECT *
FROM dbtech_thanks_entry
WHERE contentid IN(SELECT postid FROM post WHERE threadid = 429904)
	AND contenttype = 'post'
	AND userid = ?

Replace ? with your User ID.

 

[Home Alone]

Please re-download and re-upload /dbtech/thanks/hooks/newreply_form_reviewbit.php and /dbtech/thanks/hooks/printthread_post.php and let me know if that works better for you.

Anecdotal tests here on DBTech show a significant improvement, although our database is not as large as yours.

If you wish to test it yourself in phpMyAdmin, run the following query and tell me its execution time:

SELECT *
FROM dbtech_thanks_entry
WHERE contentid IN(SELECT postid FROM post WHERE threadid = 429904)
	AND contenttype = 'post'
	AND userid = ?

Replace ? with your User ID.

The changes you made were a tremendous help. Thanks.