PR https://github.com/mantisbt/mantisbt/pull/1898

@dregad I noticed that our "Manage User" page is slow.
I suspect it's caused by the user_is_email_unique calls you introduced to show the icon for non-unique emails.
You might want to remove it again, as there is not much benefit for installations with thousands of users.

I'll have a look if it can be optimized and will revert if not.

I ran quick local test with a dump of mantisbt.org tracker's user table (44423 rows):

• master (0553a42): Page execution time: 0.6156 seconds Memory usage: 3,999 KiB Total queries executed: 66 Total query execution time: 0.3678 seconds
• commit d6681928: Page execution time: 0.6105 seconds Memory usage: 4,081 KiB Total queries executed: 16 Total query execution time: 0.3592 seconds

So it does execute 50 additional queries (as expected for the uniqueness check), but the overall performance is nearly the same.

On mantisbt.org on the other hand, the same page executes in approx. 3 seconds, vs 0.2 seconds with commit d66819280.

So it seems environment specific, maybe a missing or corrupt index on the DB ?

Git bisect identifies MantisBT master bf7a3c22 as the offending commit (no surprise).

I tried optimizing and repairing the table, but that did not change anything. I don't understand why I'm not seeing the performance degradation locally. Any ideas ?

I don't understand why I'm not seeing the performance degradation locally

I remember you are using some script to change the original email addresses for local testing purposes.
Maybe you change all addresses to the same one or clear them?

[EDIT] Forget about that, the script adds the user name to the addresses.
https://github.com/mantisbt/mantisbt-tools/blob/master/db-anonymize.sql

Maybe replacing the ILIKE by comparing with UPPER(email) works better for some databases.

I remember you are using some script to change the original email addresses for local testing purposes.

I did not use it in this specific case, to make sure I had a test case as close as possible to the original. My local database is an exact copy.

Maybe replacing the ILIKE by comparing with UPPER(email) works better for some databases

It could be, but in this specific case I don't think that's the issue as both environments are running MySQL (8.0 on the server, 8.1 on my laptop).

PR https://github.com/mantisbt/mantisbt/pull/1919 improves the performance issue with manage_user_page.php reported by @atrol in 0032787:0068077.

With this, the page loads under 0.5 seconds on this tracker, vs approx. 3 seconds before this change. This is still about twice as slow as before MantisBT master d6681928, but I believe that's acceptable for an admin page.