Performance issue caused by leaderboard

[PhMemmel]

The following issue leads to a big performance impact on our platform, causing course loading times of more than 10 seconds:

To reproduce:

• As teacher in a course add a block_stash and an item
• Make sure the teacher is not in a group
• Enable leaderboard
• Enable "Show only users from my groups in the leaderboard"
• For example enable "most items" and select an item
• Verify that the table block_stash_lb_settings contains a stash item value in the column "options" (this is necessary because there seems to be a different bug where the setting is not being stored into the table sometimes)

So now the analysis what happens there (debugger or SQL log necessary):

• block_stash\manager::get_userids_for_leaderboard#1306: Here groups_get_user_groups($courseid, $user->id)[0] should retrieve the groups of the current users.
• The line below groups_get_members_ids_sql($groupids, $context) should fetch die members of these groups of the current user.
• Problem: If the user does not belong to a group, the result of groups_get_members_ids_sql($groupids, $context) is the query SELECT DISTINCT u.id FROM {user} u WHERE u.deleted = 0 which is in our case a result set of 1.8 million ids.
• The following query in for example \block_stash\local\leaderboards\most_singular_item::get_leaderboard_data then uses an IN-SQL statement with all of these 1.8 million user ids. The resulting query then takes about 11 seconds on our database and slows down the whole course page.

So you could also consider this a moodle bug that the function groups_get_members_ids_sql returns all user ids if you inject an empty array. Not sure, if this is intended behavior or not.