Partial fix Search by username takes a while

ddrager

Member
Affected version
2.1
We have quite a large userbase, 10M+ users. When doing a search by username in the administration panel, leaving all of the other fields blank, it takes about ~20 seconds to return a result. This seems to be longer than it should take given all of the other filters are blank.

Thank you for your attention!
 
To a degree, this is unavoidable, though it may primarily point to some DB tuning that would be beneficial.

The username search isn't an exact or a prefix match -- it a basic "contains" style check. Therefore, it will require MySQL to do a full table scan, so the time taken would be within MySQL. This is where InnoDB tuning and making sure that MySQL has sufficient memory (and CPU) to go through the results would help. Can you confirm whether that has been done?

There isn't likely to be much we can do here that doesn't involve more significant changes. There is a pagination check which gets the total number of results, and that does involve roughly doing the check a second time. In theory, we could avoid that if the result set is sufficiently small, though it doesn't apply if there's more than 1 page of results.
 
Top Bottom