Bug Very slow query

[Gladius]

I've been going through our slow queries today and this one accounts for 20% of them:

______________________________________________________________________ 001 ___
Count         : 163  (20.10%)
Time          : 475 s total, 2.91411 s avg, 2 s to 7 s max  (17.18%)
  95% of Time : 423 s total, 2.746753 s avg, 2 s to 5 s max
Lock Time (s) : 8 s total, 49.08 ms avg, 0 to 2 s max  (10.39%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 20 avg, 20 to 20 max  (16.48%)
Rows examined : 152.18k avg, 94.65k to 214.96k max  (58.21%)
Database      : xxxxxxxxxxxxxx
Users         : 
	admin@localhost  : 100.00% (163) of query, 62.76% (509) of all users

Query abstract:
SELECT rewards.*, user.* , avatar.avatarpath, NOT isnull(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline, customavatar.width AS avwidth, customavatar.height AS avheight, customavatar.height_thumb AS avheight_thumb, customavatar.width_thumb AS avwidth_thumb, customavatar.filedata_thumb FROM vb_dbtech_vbactivity_rewards AS rewards LEFT JOIN vb_user AS user ON (user.userid = rewards.userid) LEFT JOIN vb_avatar AS avatar ON (avatar.avatarid = user.avatarid) LEFT JOIN vb_customavatar AS customavatar ON (customavatar.userid = user.userid) WHERE user.dbtech_vbactivity_excluded_tmp = 'S' AND feature = 'S' ORDER BY dateline DESC LIMIT N, N;

Query sample:
SELECT 
		rewards.*,
		user.*
		, avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline, customavatar.width AS avwidth, customavatar.height AS avheight, customavatar.height_thumb AS avheight_thumb, customavatar.width_thumb AS avwidth_thumb, customavatar.filedata_thumb			
	FROM vb_dbtech_vbactivity_rewards AS rewards
	LEFT JOIN vb_user AS user ON (user.userid = rewards.userid)
	LEFT JOIN vb_avatar AS avatar ON (avatar.avatarid = user.avatarid)
	LEFT JOIN vb_customavatar AS customavatar ON (customavatar.userid = user.userid)
	WHERE 
		user.dbtech_vbactivity_excluded_tmp = '0'
		AND feature = 'achievement'
	ORDER BY dateline DESC
	LIMIT 20340, 20;

---

What is it and can it be improved?

There's another slow one too:

______________________________________________________________________ 003 ___
Count         : 37  (4.56%)
Time          : 153 s total, 4.135135 s avg, 2 s to 9 s max  (5.53%)
  95% of Time : 135 s total, 3.857143 s avg, 2 s to 7 s max
Lock Time (s) : 8 s total, 216.216 ms avg, 0 to 4 s max  (10.39%)
  95% of Lock : 1 s total, 28.571 ms avg, 0 to 1 s max
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 0 avg, 0 to 0 max  (0.00%)
Database      : xxxxxxxxxxxxx
Users         : 
	admin@localhost  : 100.00% (37) of query, 62.76% (509) of all users

Query abstract:
UPDATE vb_user SET dbtech_vbactivity_excluded_tmp = IF(dbtech_vbactivity_excluded = 'S' OR find_in_set(N, membergroupids) OR find_in_set(N, membergroupids) OR find_in_set(N, membergroupids) OR usergroupid IN(N3), 'S', 'S');

Query sample:
UPDATE vb_user
			SET dbtech_vbactivity_excluded_tmp = IF(dbtech_vbactivity_excluded = '1'
				OR FIND_IN_SET(17, membergroupids) OR FIND_IN_SET(8, membergroupids) OR FIND_IN_SET(18, membergroupids)
				OR usergroupid IN(17,8,18), '1', '0');

 

[Fillip H.]

That, I believe, is the "Awards" and "Achievements" lists in the Recent Changes block.

To my knowledge it cannot be improved at this time.

You can edit them out of the vbactivity template if you don't wish them to be accessible


PS: v1.1.0 of vBActivity is no longer supported, you may wish to consider upgrading to the latest version

 

[Gladius]

I've got the latest version, just put that in as I couldn't remember what it was.

I edited another slow one in, what about that one?

 

[Fillip H.]

That's a helper query for the one you posted first.

Without it, the first one would be way slower

 

[Gladius]

That, I believe, is the "Awards" and "Achievements" lists in the Recent Changes block.

To my knowledge it cannot be improved at this time.

You can edit them out of the vbactivity template if you don't wish them to be accessible

Hm, I doubt my actual members look at that often so I guess 99% of those hits are from crawlers, wasting resources. It'd be a good idea to put in a switch to show such more resource-intensive features to members only. Would you consider that?

For now I'd be happy with a quick hack to only show it for usergroup X... can you provide? Thanks in advance!

That's a helper query for the one you posted first.

Without it, the first one would be way slower

Doh! Just my luck!

 

[Fillip H.]

You can wrap it in an

<vb:if condition="is_member_of($bbuserinfo, X)"> </vb:if>

 

[Gladius]

Now if you could just point me to the exact place to do that...

 

[Fillip H.]

Wait, you're stuck on 3.8, the code will be

<if condition="is_member_of($bbuserinfo, X)"> </if>

and it goes around the <tr> tags in the dbtech_vbactivity template, under

<tr><td class="thead">$vbphrase[dbtech_vbactivity_recent_changes]</td></tr>

 

[Gladius]

Thanks, that works fine, but I just remembered that I didn't take into account that crawlers will likely have stored the url and hit it directly even if the link is not enabled any more... how could I deny the page except for user group X?

 

[Fillip H.]

By default, guests can't view vBActivity, so that's unlikely

 

[Gladius]

Well, they can on my board, I guess I turned it on for guests? If I turn it off for guests does that mean that they see no trace of it, not even achievements in posts? Because that I'd like to keep for guests to see...

 

[Fillip H.]

Nah it only controls the vbactivity.php page

 

[Gladius]

Ah, why didn't you tell me to just turn that off in the first place then?! Problem solved...

 

[Fillip H.]

Hehe, cause members will still generate those slow queries when they are accessing the page.

That being said, I did learn a useful trick for reducing query time while doing Live Wall, however I'm not entirely sure it applies to paginated areas like those in vBAA. I will be doing more tests as I work on the next version of vBAA

 

[Gladius]

That's fine, I only wanted to disable it for crawlers in the first place so leaving it on for users is ok - I don't think the actual users access those areas very often.

And cool, here's hoping the query time can be reduced here as well. I imagine this could be quite a drag on really huge boards.