Fixed Index In xf_thread

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.
 
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
 
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.
 
Top Bottom