It's not something that should happen again - basically, there's an index that has, for some reason that elludes me, failed to exist on several systems.
p_rfl is an index on the Post table ("p") on the columns post_root_id, forum_id_fk, post_latest_reply - it's used to sort the threads on the forum view page - they all have root id 0, since they are the root (non root posts have a root id that contains the post_id of the root post), forum_id_fk contains the forum_id, obviously, and post_latest_reply contains the latest reply of a post decendent (for a root post, the latest reply in the thread - but this information is known for every post in the post tree). As an optimization, we force MySQL to use that index, instead of what its optimizer thinks is right (because in this case, the optimizer usually gets it wrong). The problem is that explicitely specifying an index requires it to be there - and some installations for some reason don't have the index
.
That query adds the index.
Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com
p_rfl is an index on the Post table ("p") on the columns post_root_id, forum_id_fk, post_latest_reply - it's used to sort the threads on the forum view page - they all have root id 0, since they are the root (non root posts have a root id that contains the post_id of the root post), forum_id_fk contains the forum_id, obviously, and post_latest_reply contains the latest reply of a post decendent (for a root post, the latest reply in the thread - but this information is known for every post in the post tree). As an optimization, we force MySQL to use that index, instead of what its optimizer thinks is right (because in this case, the optimizer usually gets it wrong). The problem is that explicitely specifying an index requires it to be there - and some installations for some reason don't have the index
. That query adds the index.
Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com
