Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-10712

Performance: Nearly every query against phpbb_drafts misses index

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects Version/s: 3.0.10
    • Fix Version/s: 3.0.15-RC1
    • Component/s: Posting
    • Labels:
      None
    • Environment:
      MySQL 5.1 with InnoDB tables

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                CHItA CHItA
                Reporter:
                thenickdude thenickdude
              • Votes:
                1 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: