Performance: Moving topic does not use index

When a topic is moved to another forum, this query is run to update phpbb_topics_track:

UPDATE phpbb_topics_track SET forum_id =? WHERE topic_id =?

The two indexes on this table are (userid, topicid) and (forumid), neither of which can effectively assist with this query. The forumid index is used to look for all topics not already in the destination forum, which is probably most of them. So, on our server, a WHERE clause must be applied to 1.2 million rows in order to find the old topic.

Adding an index on topic_id would solve the problem, but it would also increase index space and slow down operations on this table. But you can use the forum_id index that already exists if the query is modified to:

UPDATE phpbb_topics_track SET forum_id =? WHERE topic_id =? AND forum_id=?

(using the old forum_id of the topic to help narrow the search)

This is dramatically faster for sites with more than one forum.