Slow Query on Memberlist realname start character search

[albertlast]

Description

When you go to the memberlist and use the jump list in the right top, for exp. start with n https://www.simplemachines.org/community/index.php?action=mlist;sa=all;start=n#lettern where you can jump to a start character for realname the system takes long.

Environment (complete as necessary)

• Version/Git revision: latest
• Database Type: pg and mysql
• Database Version:
• PHP Version:

Additional information/references

https://github.com/SimpleMachines/SMF2.1/blob/54b854bba9abddf1f351ab48eed0edc347027366/Sources/Memberlist.php#L246-L255

generated sql:

SELECT COUNT(*)
FROM smf_members
WHERE LOWER(SUBSTRING(real_name, 1, 1)) < 'n'
   AND is_activated = 1 

[albertlast]

alternative solution would be to drop this function.

[DiegoAndresCortes]

We only encounter this slow load in big forums, and even then 2 seconds it's not too excessive (at least for your url example)

[albertlast]

well the issue is that sutch function are target for ddos, and also this function "hidden" behind login doesn't help.

Also in my mid size test forum is the loading time noticeable.

[live627]

We must also ask ourselves if adding this index is worth it and consider the implications of adding an index for a single function.

[albertlast]

Same you could ask for the function, if they worth.

I mean end of the when sites like sm.org get down by function like this you had to decide.

[jdarwood007]

For sm.org, it isn't that slow. yes its about 2 seconds, but not horribly slow. We are hitting indexes completely here. So the only thing would be if we had a index on the first letter. MySQL can do that with indexes. Not sure if it would help though.

[albertlast]

an index on the first letter should not solve this for mysql.

[Sesquipedalian]

I think we should just get rid of this feature. It only works for Latin scripts, and not even for all of those.

We'll leave it alone for 2.1.x, but remove it in 3.0.