Too many bans and gags - on player connected join lag

[armanossiloko]

A friend of mine runs a Left 4 Dead 2 10v10 versus server and the SourceBans database is filled with way too many bans and gags.

What happens?

With the number of bans being really huge, once a player joins the game, when SourceBans performs the check "how many bans and gags the player has", it lags the server out for about a second or two, most likely due to the query that is being executed not utilizing any indexes, because it uses regex to filter bans and gags.

SELECT COUNT(bid) FROM %s_bans WHERE ((type = 0 AND authid REGEXP '^STEAM_[0-9]:%s$') OR (type = 1 AND ip = '%s'))
UNION
SELECT COUNT(bid) FROM %s_comms WHERE authid REGEXP '^STEAM_[0-9]:%s$'

Any other comments?

One thing that seems to have fixed it for the server I mentioned above was to rewrite the sbpp_checker query that retrieves the number of gags and bans once a player joins the game.

[armanossiloko]

I have made a PR based on what seems to have helped in this case. Another potential thing I can think of is creating an index on the type and ip column(s) in the sb_bans table. I haven't done that in the case of the #896 pull request

[Rushaway]

Can be closed