IPs and ranges saved in strings in SQL #54

New Issue

hf · 2023-03-17T03:10:45Z

hf commented

2023-03-17 03:10:45 +00:00

IPs and CIDR are saved in strings in the database, which is bad for performance and does not allow us to do calculations (like if an IP is in a given range) on the sql server.

IPv4 is binary(4) and IPv6 is binary(16) (128bit), which means we also have to split into sperate tables for v4 and v6.
Which now introduces super ugly issues in relations. i.e. if the user table links to the most recently used IP, we need a column telling us if its v4 or v6 it points too.

Or we store IPv4 as IPv6. Which causes it to take four times more space than needed (but still less than a string), but is the easiest to deal with.

Or we do that, but also drop half of the IPv6, and store in binary(8), which is 64bit.
Which would be our own weird format, but most suitable, because we do not care about the last digits.

IPs and CIDR are saved in strings in the database, which is bad for performance and does not allow us to do calculations (like if an IP is in a given range) on the sql server. IPv4 is binary(4) and IPv6 is binary(16) (128bit), which means we also have to split into sperate tables for v4 and v6. Which now introduces super ugly issues in relations. i.e. if the user table links to the most recently used IP, we need a column telling us if its v4 or v6 it points too. Or we store IPv4 as IPv6. Which causes it to take four times more space than needed (but still less than a string), but is the easiest to deal with. Or we do that, but also drop half of the IPv6, and store in binary(8), which is 64bit. Which would be our own weird format, but most suitable, because we do not care about the last digits.

hf added this to the SQLRefactor milestone 2023-03-17 03:10:45 +00:00

hf added the

enhancement

label 2023-03-17 03:10:45 +00:00

hf self-assigned this 2023-03-17 03:10:45 +00:00

hf added

bug

and removed

enhancement

labels 2023-03-17 15:07:18 +00:00

hf added the

Priority: High

label 2023-03-17 15:09:42 +00:00

hf commented

2023-03-17 15:11:10 +00:00

Current IPInfo table takes multiple seconds to query with its >2 million rows of inefficiently saved data.
Biggest performance issue of the game currently.

Current IPInfo table takes multiple seconds to query with its >2 million rows of inefficiently saved data. Biggest performance issue of the game currently.

Sign in to join this conversation.