Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-10712

Performance: Nearly every query against phpbb_drafts misses index

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Won't Fix
    • Icon: Minor Minor
    • 3.0.15-RC1
    • 3.0.10
    • Posting
    • None
    • MySQL 5.1 with InnoDB tables

      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.

            CHItA CHItA
            thenickdude thenickdude [X] (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: