Optimizing viewtopic query

[hapablap]

Hello,
I help doing admin tasks in a relatively big board (15M posts) running phpBB 3.0.5 and MySQL, and these days we were having some load problems. I am no expert in databases, so if something that I am about to say is wrong, please do tell me.

Code: Select all

SELECT t.topic_id
   FROM topics t
   WHERE t.forum_id = (...)
      AND t.topic_type IN (0, 1)
      AND t.topic_deleted = 0
      AND t.topic_approved = 1
   ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
LIMIT 50

The server sometimes was struggling with dozens of queries like that. So looking at the PROCESSLIST we saw that the hard part for this query was the "Sorting result" phase. With an explain, we could see that it was spending lots of time with a filesort process. So we added an index on 'topic_type' and 'topic_last_post_time' fields. But MySQL was ignoring the index (I've read this is a common bug with the MySQL optimizer...), so we added a FORCE INDEX(name_of_the_new_index). The 'Using filesort' parte disappeared from the EXPLAIN, and the load went down by orders of magnitude!

I am writing just if it's useful for somebody, or if it's useful for the development of the next version of phpBB.

PS: right now we are in process of tuning more queries and indexes, I'll keep you updated

[hapablap]

The 'sessions' table was being a problem too. Even it was on memory, it was doing always full scans in this query:

Code: Select all

 SELECT s.session_user_id, s.session_ip, s.session_viewonline
      FROM sessions s
      WHERE s.session_time >= ... AND s.session_user_id <> 1

The problem here lied in that the default index type of this table (HASH) does not support queries with >, <, >= or <=. On the other hand, BTREE does. So doing this produced faster queries in the sessions table, converting the full scans (7000+ rows!) in 4-5 rows scans:

Code: Select all

ALTER TABLE sessions DROP INDEX session_time, ADD INDEX session_time USING BTREE(session_time)

[naderman]

In general we do not add such parts to the query because they are MySQL specific and phpBB supports many other DBMSs.

Code: Select all

SELECT t.topic_id
   FROM topics t
   WHERE t.forum_id = (...)
      AND t.topic_type IN (0, 1)
      AND t.topic_deleted = 0
      AND t.topic_approved = 1
   ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
LIMIT 50

[...]With an explain, we could see that it was spending lots of time with a filesort process.

More specifically I'd be rather surprised to see a regular viewforum read access trigger a filesort. The query does look like you installed a MOD though since phpBB does not usually have a topic_deleted column. Maybe that MOD modified your indexes or failed to modify them correctly to make MySQL use the right index by default?

[hapablap]

In general we do not add such parts to the query because they are MySQL specific and phpBB supports many other DBMSs.

Seems a logical thing to do. But maybe with this?

More specifically I'd be rather surprised to see a regular viewforum read access trigger a filesort. The query does look like you installed a MOD though since phpBB does not usually have a topic_deleted column.

Whoops! You are right

But let me explain. I am not the main admin of the board; in the past there was another person doing that, and I was not aware of that modification. Anyway, I verified the indexes of the 'topics' table are the same as in a stock phpBB, using the original query results in a filesort, and just to be sure I did more tests with a clean just-installed phpBB 3.0.5. I inserted 192 new topics, and the result was pretty much the same:

Code: Select all

mysql> EXPLAIN SELECT t.topic_id FROM phpbb_topics t WHERE t.forum_id = 2 AND t.topic_type IN ( 0, 1 )  ORDER BY t.topic_type DESC , t.topic_last_post_time DESC LIMIT 50;
+----+-------------+-------+-------+-------------------------------------------------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys                                         | key            | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+-------------------------------------------------------+----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t     | range | forum_id,forum_id_type,forum_appr_last,fid_time_moved | fid_time_moved | 3       | NULL |  185 | Using where; Using filesort |
+----+-------------+-------+-------+-------------------------------------------------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)


Removing the ORDER BY part will not trigger the filesort (obvious I guess?):

Code: Select all

mysql> EXPLAIN SELECT t.topic_id FROM phpbb_topics t WHERE t.forum_id = 2 AND t.topic_type IN ( 0, 1 ) LIMIT 50;
+----+-------------+-------+------+-------------------------------------------------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                                         | key            | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------------------------------------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | forum_id,forum_id_type,forum_appr_last,fid_time_moved | fid_time_moved | 3       | const |  185 | Using where |
+----+-------------+-------+------+-------------------------------------------------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)


Adding the above mentioned index (topic_type + topic_last_post_time) and forcing it seems to do the trick:

Code: Select all

mysql>  ALTER TABLE phpbb_topics ADD INDEX my_index (topic_type, topic_last_post_time);
Query OK, 192 rows affected (0.03 sec)
Records: 192  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT t.topic_id FROM phpbb_topics t WHERE t.forum_id = 2 AND t.topic_type IN ( 0, 1 )  ORDER BY t.topic_type DESC , t.topic_last_post_time DESC limit 50;
+----+-------------+-------+-------+----------------------------------------------------------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys                                                  | key            | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+----------------------------------------------------------------+----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t     | range | forum_id,forum_id_type,forum_appr_last,fid_time_moved,my_index | fid_time_moved | 3       | NULL |  185 | Using where; Using filesort |
+----+-------------+-------+-------+----------------------------------------------------------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT t.topic_id FROM phpbb_topics t force index(my_index) WHERE t.forum_id = 2 AND t.topic_type IN ( 0, 1 )  ORDER BY t.topic_type DESC , t.topic_last_post_time DESC LIMIT 50;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | range | my_index      | my_index | 1       | NULL |  188 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.01 sec)

Edit: I corrected the queries, I was forgetting the LIMIT 50.

[tornado_mtb]

Thanks! I add your comment about session to using btree. result is good.

but for the my_index , if user don't use sort by "topic_last_post_time", what is happen?
and which source file do you modify put force to?