Fixed Index In xf_thread

[digitalpoint]

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

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.

 

[Adam Howard]

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

 

[Mike]

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

 

[Mike]

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.