SQL query retrieving forum discussions on the mod/forum/view.php was taking between 200 and 500 seconds, even on the forums with 20-30 discussions. After a fix (see below) the query takes under 1 second.

This was happening on 4.1 but the code did not change since then so it would be the same problem on any current Moolde version.

Steps to reproduce:

• Have a very big database, with a lot of forums, in this case there were over a million discussions (and probably several millions of posts)
• Open any forum page, it must have some discussions but does not have to have many
• Change sort order to sort by the number of replies
• boom, your query now takes forever to execute

Backtrace of a slow query from moodle dblog

-- line 471 of /mod/forum/classes/local/vaults/discussion_list.php: call to mysqli_native_moodle_database->get_records_sql()

-- line 6887 of /mod/forum/lib.php: call to mod_forum\local\vaults\discussion_list->get_from_forum_id_and_group_id()

-- line 180 of /mod/forum/classes/local/renderers/discussion_list.php: call to mod_forum_get_discussion_summaries()

-- line 259 of /mod/forum/view.php: call to mod_forum\local\renderers\discussion_list->render() |