Performance: Nearly every query against phpbb_drafts misses index

phpbb_drafts has a primary index (draft_id), and a secondary index (save_time).

It is not useful to find every draft written by anybody and sort them by save_time. In fact, all queries that phpBB issues which order by save_time also test user_id in the WHERE clause, so the save_time index cannot be used. This is the case in both ucp_main.php and functions_posting.php, the two places where save_time is ordered on.

There is no index on phpbb_drafts which can be used to find the drafts for a given user. This means that when composing a post or PM, every draft must be examined. Here is a typical query followed by explain output:

EXPLAIN SELECT draft_id

FROM phpbb_drafts

WHERE user_id = 149346 AND forum_id = 66 AND topic_id = 941253

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

1, SIMPLE, phpbb_drafts, ALL, NULL, NULL, NULL, NULL, 94817, "Using where"

Averaged over 143 such queries on my live system, the average execution time of this query is 18ms, which is very slow.

I suggest that the index (save_time) be dropped and replaced by an index (user_id, save_time). With this change, the same query as before now has this explain and executes extremely quickly:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

1, SIMPLE, phpbb_drafts, ref, user_id_save_time, user_id_save_time, 3, const, 12, Using where

Note the decrease in rows from 94817 to 12.