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

Double query?

Discussion in 'Troubleshooting and Problems' started by RobParker, Jan 31, 2013.

  1. RobParker

    RobParker Well-Known Member

    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?
     
  2. Jon W

    Jon W Well-Known Member

    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.
     
    Jake Bunce likes this.

Share This Page