Code: Select all
SELECT ug.user_id, a.forum_id, r.auth_setting, r.auth_option_id, ao.auth_option
FROM phpbb_acl_groups a, phpbb_user_group ug, phpbb_groups g, phpbb_acl_roles_data r, phpbb_acl_options ao
WHERE a.auth_role_id = r.role_id AND r.auth_option_id = ao.auth_option_id
AND a.group_id = ug.group_id
AND g.group_id = ug.group_id
AND ug.user_pending = 0
AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
AND ao.auth_option = 'a_'
Code: Select all
SELECT ug.user_id, a.forum_id, r.auth_setting, r.auth_option_id, ao.auth_option
FROM phpbb_acl_groups a, phpbb_user_group ug, phpbb_groups g, phpbb_acl_roles_data r, phpbb_acl_options ao
WHERE a.auth_role_id = r.role_id AND r.auth_option_id = ao.auth_option_id
AND a.group_id = ug.group_id
AND g.group_id = ug.group_id
AND ug.user_pending = 0
AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
AND ao.auth_option = 'm_'
I've tried a few things. phpBB has an option to pause server access if the server load is too high. However, that won't help because this still triggers these two queries (found in auth.php).
Another approach might be to let the SQL server cache this, by running the query preemptively when the server loads and before phpBB starts. This is a bad idea, but also is less relevant for MYSQL 8 (with MYSQL 5.6-5.7, it has a built-in memory cache that might be relevant).
I think this query is quite badly written (join on was invented to prevent exactly this type of mistake), there's also no way to explicitly load the query while blocking others while it happens, despite the output of this query rarely being changed. A simple solution to this would be to run it off a physical file that changes when the administrator or moderator lists change using separate dedicated files not using the normal cache infrastructure.
since the problem is the joins, indexes are not necessarily that useful, but I am giving it a try.
One flaw that I see with the current index design is that the indexes are not necessarily in the ideal order or composition. Although the query uses all 3 values (group_leader, group_id and user_pending) it seems that separating the indexes has a bigger chance of success. BTW, this might be further improved by adding more indexes (using group_id and user_pending) but this specific index made the most dramatic change:
Code: Select all
ALTER TABLE `phpbb_user_group` ADD INDEX `auth_idx` (`user_pending`, `group_leader`);
after the change: 1525 rows are pulled from phpbb_user_group (ug). Since this is a Cartesian multiplication, this should be a substantial performance boost.