One of the admins in my old gaming community noticed sourcebans was really slow loading the bans pages, both on the admin side and user-facing side. I enabled the slow query logger and immediately found the root of the problem to be a 30-second query from PruneBans().

What are the steps to reproduce this issue?

1. At least 7849 rows matching: SELECT DISTINCT authid FROM `sb_bans` WHERE `type` = 0 AND `RemoveType` IS NULL;
2. At least 183 rows matching: SELECT DISTINCT ip FROM `sb_bans` WHERE `type` = 1 AND `RemoveType` IS NULL;
3. Load any page with bans

What happens?

Page takes 30 seconds to load.

What were you expecting to happen?

No noticeable increase to load time.

Any logs, error output, etc.?

Notice: Please read the section below before trying to figure out why there's so may things in this gist. https://gist.github.com/rannmann/650b9fe26060b1a1e4a7e9b9e64e8530

Also note that by simply adding an index the rows examined drops from 18.7 million to 3.7 million. The sub-selects in the update statement appear to be running per row matched, not just one time.

Any other comments?

I wrote a rough draft fix for it. I'm not really familiar with the sourcebans codebase, and honestly I have no idea what this query is supposed to be accomplishing. But there are two key takeaways on improving this, and one further recommendation.

1. Index the field being searched on.
2. Don't run sub-queries in update statements.
3. Recommendation: Run these types of cleanup tasks after results are rendered. Find instances of PruneBans(); calls and toss them into register_shutdown_function(). Users shouldn't have to wait for these calls to process.

I realize PruneBans() has changed in master since the latest release, but the table schema and query in appear to be unchanged. Due to the PDO change in master, binding all the steam IDs and IP addresses is recommended rather than injecting them into the query without any kind of escaping.

What versions of software are you using?

Operating System: Ubuntu
SourceBans++ Version: 1.6.3
PHP Version: 5.6.40
MySQL Version: 5.7.28
Link to your project: https://firepoweredgaming.com/sourcebanspp/
Link to a phpinfo() output: https://firepoweredgaming.com/sourcebanspp/phpinfo.php