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

Fixed Query plan is not optimal while selecting from session_activity

Discussion in 'Resolved Bug Reports' started by Pepelac, Oct 18, 2013.

  1. Pepelac

    Pepelac Well-Known Member

    I clearly understand that this is not a XF issue, but...

    This query is not using index on the view_date column
    SELECT session_activity.*
    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 > 1382086508)
    ORDER BY session_activity.view_date DESC
    It could be "fixed" by forcing an index. For example, query plan before forcing the index on view_date column:

    and after forcing:

    Yoskaldyr likes this.
  2. digitalpoint

    digitalpoint Well-Known Member

    Is the query with the forced index faster? There are times that the overhead of using an index can actually make the query slower.
  3. Yoskaldyr

    Yoskaldyr Well-Known Member

    before FORCE INDEX
    Run Time: 0.055693
    with FORCE INDEX
    Run Time: 0.027352
    Marcus and Steve F like this.
  4. Mike

    Mike XenForo Developer Staff Member

    Forcing the index shouldn't work normally. That index is likely a hash index rather than a btree so range selections don't work. If you're affected (though this table will usually be small anyway; if it grows too big, it may be indicative of cleanup crons not running), you can just change the index to btree.

    I'm not going to change this in a bug fix release though, so it would be resolved in something like 1.3.
  5. Mike

    Mike XenForo Developer Staff Member

    The index format has been changed to be used correctly for 1.3.

Share This Page