Partial fix Search by Email - Slow on Large User Database

Affected version
2.2.3

ddrager

Member
When trying to locate a user by email (used for GDPR removal requests), search takes a very, very long time and sometimes times out.

We have a large 12M+ member database.

It seems like the cause is that email search is using

Code:
LIKE '%email%'

Is it possible to refactor user search, so that if there is a single search parameter, such as username or email, that it uses an exact search instead of a LIKE? Or perhaps a switch like "Search exact username" to force that? I know this can get complicated so some thought would have to go into how to make the UI experience optimal.

Let me know if I can provide any further information.
 

PaulB

Well-known member
Switching from %email% to email% would also be efficient; the former requires a full table scan, whereas the latter is just a range query.
 

Kirby

Well-known member
A checkbox would definitly work, but it might be easier to just do the following:

If the input is a full email address, than take this as exact (and maybe show a link "There might be more results for for similar adresses" which would trigger a like query).
Most likely admins would search for smth. that is only part of an email (but does not look like a complete one) or an exact email?
 

ddrager

Member
A checkbox would definitly work, but it might be easier to just do the following:

If the input is a full email address, than take this as exact (and maybe show a link "There might be more results for for similar adresses" which would trigger a like query).
Most likely admins would search for smth. that is only part of an email (but does not look like a complete one) or an exact email?
I really like this idea. If it is a fully formed email address (checked on frontend by regex), it switches to an exact search (maybe with an indicator?). Optionally, give ability to disable exact search at that point.
 

XF Bug Bot

XenForo bug fixer bot
Staff member
Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.2.6).

Change log:
Add the ability to perform exact match email searches
There may be a delay before changes are rolled out to the XenForo Community.
 
Top