Optimize IP address query performance

[diox]

Searching for users by ip address is currently a bit slow, the data is scattered on different tables, IPs are stored in string form as a varchar(45), and not always indexed.

One fix to investigate would be to store them as blob or varbinary, using INET6_ATON() and friends in MySQL, or ipaddress.ip_address().packed in python) and indexing that. That's blocked by #7673 because we currently do manual SQL queries with FIND_IN_SET(), which would definitely not work anymore with that approach.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. If you think this bug should stay open, please comment on the issue with further details. Thank you for your contributions.

[wagnerand]

We should either fix this or remove the feature again.

[diox]

Biggest issue with the query right now seems to be the OR + multiple tables search. MySQL ends up doing a full table scan on users. So even though optimizing storage of the IPs in a binary column would help, I don't think that would solve it.

Couple ideas to fix this:

• Rewrite the query to use a UNION DISTINCT instead of DISTINCT + multiple OR - the UNION DISTINCT would be costly, but if we don't have that many results most of the time, certainly less costly than a full table scan. The problem with this approach is it makes finding results easy but displaying them with what caused each row to be returned a bit more annoying, especially with the ORM.
• Stop searching through various tables - consolidate all IP logging into log_activity_ip (IPLog model) and only use that. Would require a massive data migration to backfill the table.

[diox]

Going to try consolidating all IP logging into log_activity_ip, that seems to be the more promising approach.

I've filed #8845 and #8853.

A summary of what will be implemented in those issues:

• Every time a restriction is triggered, an activity log and ip log will be recorded. We'll backfill data for users that logged in in 2019 and later.
• Every time someone logs in, an activity log and ip log will be recorded. We'll backfill data for users that logged in in 2019 and later.
• Every time someone posts a rating, an activity log and ip log will be recorded, but they will only be kept for 180 days. We won't backfill data there.

Once this is done, I'll modify the IP address admin search code to only search in IPLog.