Searching for users by username timing out

Affected version
XenForo 2.2.13

I believe that a change made to XF\Admin\Controller\User at line 78 might be causing the username index hint to be ignored unintentionally. In our installation, this is causing all AdminCP searches for users by their username (with "Exact match" ticked) to time out. I'm assuming that our registered user count of 6,366,411 might have something to do with this as well, but that wasn't an issue with the index not ignored.

A look at this MySQL slow query log entry caused after initiating a user search by username in the AdminCP might give more insight into the issue:
# Time: 2023-05-24T18:41:09.152587Z
# Schema: xenforo  Last_errno: 0  Killed: 0
# Query_time: 15.141075  Lock_time: 0.000056  Rows_sent: 1  Rows_examined: 6366342  Rows_affected: 0
# Bytes_sent: 4242
use xenforo;
SET timestamp=1684953654;
SELECT `xf_user`.*
            FROM `xf_user` IGNORE INDEX (`username`)

            WHERE (`xf_user`.`username` = 'Externalizable') AND (`xf_user`.`email` LIKE '%%')
            ORDER BY `xf_user`.`username` ASC


The same query with the username index not ignored completes after just 583ms, naturally. Is this new behavior intended?

Thank you!
Top Bottom