Partial fix Search by Email - Slow on Large User Database

[ddrager]

Affected version

2.2.3

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

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]

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]

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]

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]

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.