Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
3.0.7-PL1
-
None
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.