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

Performance: viewforum requires filesort of all topics

    Details

      Description

      This query in viewforum.php is called to fetch the ID of topics that will be displayed on the current page:

      $sql = 'SELECT t.topic_id
      	FROM ' . TOPICS_TABLE . " t
      	WHERE $sql_where
      		AND t.topic_type IN (" . POST_NORMAL . ', ' . POST_STICKY . ")
      		$sql_approved
      		$sql_limit_time
      	ORDER BY t.topic_type " . ((!$store_reverse) ? 'DESC' : 'ASC') . ', ' . $sql_sort_order;
      

      In practice that expands to something like:

      SELECT t.topic_id
      FROM phpbb_topics t
      WHERE t.forum_id =32
      AND t.topic_type
      IN ( 0, 1 ) 
      AND t.topic_approved =1
      ORDER BY t.topic_type DESC , t.topic_last_post_time DESC 
      LIMIT 35
      

      Which has this explain:

      +----+-------------+-------+------+----------------------------------------------------------------------+----------+---
      ------+-------+-------+-----------------------------+
      | id | select_type | table | type | possible_keys                                                        | key      | ke
      y_len | ref   | rows  | Extra                       |
      +----+-------------+-------+------+----------------------------------------------------------------------+----------+---
      ------+-------+-------+-----------------------------+
      |  1 | SIMPLE      | t     | ref  | forum_id,forum_id_type,topic_approved,fid_time_moved,forum_appr_last | forum_id | 3
            | const | 24184 | Using where; Using filesort |
      

      The indexes chosen depend on exactly what you have in the forum, but note that there is no index that allows a filesort to be avoided. As this is an extremely common query, avoiding paging through a filesorted resultset would be nice. That query takes 0.125 seconds on my machine on one particular forum.

      If an index is added to phpbb_topics `topic_order` (`forum_id`,`topic_approved`,`topic_type`,`topic_last_post_time`), the explain output is:

      +----+-------------+-------+------+----------------------------------------------------------------------------------+--
      -----------+---------+-------------+-------+-------------+
      | id | select_type | table | type | possible_keys                                                                    | k
      ey         | key_len | ref         | rows  | Extra       |
      +----+-------------+-------+------+----------------------------------------------------------------------------------+--
      -----------+---------+-------------+-------+-------------+
      |  1 | SIMPLE      | t     | ref  | forum_id,forum_id_type,topic_approved,fid_time_moved,forum_appr_last,topic_order | t
      opic_order | 4       | const,const | 24184 | Using where |
      +----+-------------+-------+------+----------------------------------------------------------------------------------+--
      -----------+---------+-------------+-------+-------------+
      

      And the running time becomes 0.000 seconds. The forum tested has about 50,000 topics.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: