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

Fixed Index In xf_thread

Discussion in 'Resolved Bug Reports' started by digitalpoint, Nov 30, 2012.

  1. digitalpoint

    digitalpoint Well-Known Member

    The query that runs to find how many threads are in a forum (every time you view a forum) is something like this...

    Code:
    SELECT COUNT(*)
    FROM xf_thread AS thread
    WHERE (thread.node_id = 27) AND (thread.sticky = 0) AND (thread.discussion_state IN ('visible','deleted','moderated'))
    There is not an index that can be fully utilized since no index has discussion_state in it. If your forum has a lot of threads in it and can cause noticeable speed issued with a high number of threads in a forum (more than a second to run the query).

    While I've already done it on my setup, I'd suggest an index with the 3 fields: node_id, sticky, discussion_state be added to the xf_thread table for scalability reasons for others.
     
    Eagle, Deebs, Jake Bunce and 6 others like this.
  2. Adam Howard

    Adam Howard Well-Known Member

    Maybe worth noting that discussion_state is located in the following templates.
    • thread_list_item
    • thread_list_item_deleted
    • thread_list_item_edit
    • thread_view
    • watch_threads_list_item


    Off topic, also was found in 1 other 3rd party add-on ******_Tags_thread_list_item
     
  3. Mike

    Mike XenForo Developer Staff Member

    The sort of thing I will look into for 1.2. (So just moving to future fix for now.)
     
  4. Mike

    Mike XenForo Developer Staff Member

    So I have changed this index for 1.2 (dropping the previous one that included sticky, so basically just adding discussion_state), but it still needs some more testing in practice. Members (and admins) get more than one discussion_state, so I'm concerned about negative effects on the query that gets the data as it would be more efficient to simply use the node_id+last_post_date index in that case, but I don't know if MySQL is smart enough to do that in various scenarios.
     

Share This Page