bug: COALESCE function causes slow query

[TrueRou]

Describe the bug

repositories that uses mysql coalesce function will NOT hit index, which causes slow query and performance dropped. here is the comparison, query with coalesce is obviously cost disastrous more time (our server has make map_md5 as index in scores table)

To Reproduce

1. players played normally in the server
2. player reported that server is slow and laggy
3. check server, mysqld process has full cpu usage
4. check mysql slow query log and it shows many slow querys

Expected behavior

find a way to make coalesce hits index or don't use coalesce anymore

bancho.py Version

4.7.2

Python Version

3.9.X (Default)

Relevant log output

An example extry of mysql slow query log
# User@Host: gulag[gulag] @ localhost [::1]  Id:    47
# Query_time: 5.227328  Lock_time: 0.000079 Rows_sent: 0  Rows_examined: 1176231
SET timestamp=1674828621;
SELECT id, map_md5, score, pp, acc, max_combo, mods, n300, n100, n50, nmiss, ngeki, nkatu,
grade, status, mode, play_time, time_elapsed, client_flags, userid, perfect, online_checksum

          FROM scores
         WHERE map_md5 = COALESCE('7bdcf5615400d8499b21c24c26fdb360', map_md5)
           AND mods = COALESCE(NULL, mods)
           AND status = COALESCE(2, status)
           AND mode = COALESCE(0, mode)
           AND userid = COALESCE(5347, userid);

Additional context

No response

[arily]

I have to point out that we are using MySQL5.7, as I asked in discord server and someone pointed out that MySQL8 won't be affected.

If there's no intention to address this bug, You may want to change the minimum requirement to MySQL>=8 in the readme file.