• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Double query?

RobParker

Well-known member
#1
I'm hoping someone can explain this to me.

If I look at my queries on forumhome in debug mode I see the following:

This is Query #5
Code:
SELECT session_activity.*
,
user.*,
user_profile.*,
user_option.*
,(select sum(count_received) from dark_postrating_count where user_id = user.user_id and rating in (1,2,4,5,6,7,8,10)) as positive_rating_count
 
,(select sum(count_received) from dark_postrating_count where user_id = user.user_id and rating in (14,3,11,12,13,18)) as negative_rating_count
 
,(select sum(count_received) from dark_postrating_count where user_id = user.user_id and rating in (9,15,16,17)) as neutral_rating_count
 
FROM xf_session_activity AS session_activity
 
LEFT JOIN xf_user AS user ON
(user.user_id = session_activity.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
WHERE (session_activity.view_date > 1359639813)
ORDER BY session_activity.view_date DESC
 
Run Time: 0.084260
Select Type Table Type Possible Keys Key Key Len Ref Rows Extra
PRIMARY session_activity ALL view_date 4146 Using where; Using filesort
PRIMARY user eq_ref PRIMARY PRIMARY 4 wwwidea_scxf.session_activity.user_id 1
PRIMARY user_profile eq_ref PRIMARY PRIMARY 4 wwwidea_scxf.user.user_id 1
PRIMARY user_option eq_ref PRIMARY PRIMARY 4 wwwidea_scxf.user.user_id 1
DEPENDENT SUBQUERY dark_postrating_count ref user_id_rating user_id_rating 4 wwwidea_scxf.user.user_id 3 Using where
DEPENDENT SUBQUERY dark_postrating_count ref user_id_rating user_id_rating 4 wwwidea_scxf.user.user_id 3 Using where
DEPENDENT SUBQUERY dark_postrating_count ref user_id_rating user_id_rating 4 wwwidea_scxf.user.user_id 3 Using where
and this is query #11

Code:
SELECT session_activity.*
,
user.*,
user_profile.*,
user_option.*
,(select sum(count_received) from dark_postrating_count where user_id = user.user_id and rating in (1,2,4,5,6,7,8,10)) as positive_rating_count
 
,(select sum(count_received) from dark_postrating_count where user_id = user.user_id and rating in (14,3,11,12,13,18)) as negative_rating_count
 
,(select sum(count_received) from dark_postrating_count where user_id = user.user_id and rating in (9,15,16,17)) as neutral_rating_count
 
FROM xf_session_activity AS session_activity
 
LEFT JOIN xf_user AS user ON
(user.user_id = session_activity.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
WHERE (session_activity.view_date > 1359639813)
ORDER BY session_activity.view_date DESC
 
Run Time: 0.088155
Select Type Table Type Possible Keys Key Key Len Ref Rows Extra
PRIMARY session_activity ALL view_date 4146 Using where; Using filesort
PRIMARY user eq_ref PRIMARY PRIMARY 4 wwwidea_scxf.session_activity.user_id 1
PRIMARY user_profile eq_ref PRIMARY PRIMARY 4 wwwidea_scxf.user.user_id 1
PRIMARY user_option eq_ref PRIMARY PRIMARY 4 wwwidea_scxf.user.user_id 1
DEPENDENT SUBQUERY dark_postrating_count ref user_id_rating user_id_rating 4 wwwidea_scxf.user.user_id 3 Using where
DEPENDENT SUBQUERY dark_postrating_count ref user_id_rating user_id_rating 4 wwwidea_scxf.user.user_id 3 Using where
DEPENDENT SUBQUERY dark_postrating_count ref user_id_rating user_id_rating 4 wwwidea_scxf.user.user_id 3 Using where
These two queries add up to over 90% of the time taken on forumhome so if they are doubling up it'd be good to understand why.

Any ideas? Or have I misunderstood something?
 

Jon W

Well-known member
#2
I only see it once on my installation. Might be worth disabling a few add-ons if you can and see if that makes it disappear. If you can narrow it down to one add-on, then perhaps the author of that add-on will be able to help.

I would start with the post rating add-on that seems to be adding to that query.