DEV Community

yuanke215
yuanke215

Posted on Originally published at ipcalcplus.com AI-assisted

Stop Storing IP Addresses as Strings: The Integer Trick That Makes Your Queries 10x Faster

#database #devops #performance #python

Your log table has 80 million rows. The client_ip column is a VARCHAR(15), indexed, and every analytics query that filters by IP range takes three seconds. The DBA shrugs: "that's just how IPs are stored."

It isn't. An IPv4 address is a 32-bit unsigned integer — 192.168.1.1 is literally 0xC0A80101 is literally 3232235777. Every time you store it as a string, you pay the price in storage, index size, and range-query performance. And you pay it again on every single query.

The number hiding inside every IPv4 address

An IPv4 address is four octets, each 8 bits, concatenated into one 32-bit number:

192.168.1.1

octet 1: 192 = 11000000
octet 2: 168 = 10101000
octet 3:   1 = 00000001
octet 4:   1 = 00000001

32-bit value = 11000000101010000000000100000001
decimal      = 3232235777
Enter fullscreen mode Exit fullscreen mode

The math: 192 × 256³ + 168 × 256² + 1 × 256 + 1 = 3232235777.

That single integer is all the information in the address. Everything else — dotted decimal, hex, the network and host portions — is a view of those same 32 bits.

What string storage costs you

Concern VARCHAR(15) INT UNSIGNED
Storage per row 15 bytes (or more with utf8mb4) 4 bytes
Index size large, poor locality compact, sequential
Range query (BETWEEN) string comparison, lexicographic surprises native integer comparison
Membership check regex/full scan territory BETWEEN on an index

The lexicographic trap is the sneaky one. SELECT ... WHERE client_ip BETWEEN '10.1.0.1' AND '10.1.0.255' looks right but string comparison orders '10.1.0.255' < '10.1.1.0' — while '10.2.0.1' < '10.1.0.255' is also true, so a naive lexicographic range silently includes 10.2.x.x addresses. Every "why is my IP filter returning junk" ticket traces back to this.

Doing it right in MySQL 

-- Store the integer, index it, done
CREATE TABLE access_log (
    id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_ip INT UNSIGNED NOT NULL,          -- 4 bytes, not 15
    ...
    INDEX idx_client_ip (client_ip)
);

-- Insert
INSERT INTO access_log (client_ip) VALUES (INET_ATON('192.168.1.1'));

-- Range query: every address in 192.168.1.0/24
SELECT * FROM access_log
WHERE client_ip BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');

-- Read it back
SELECT INET_NTOA(client_ip) FROM access_log;
Enter fullscreen mode Exit fullscreen mode

INET_ATON and INET_NTOA are built into MySQL and MariaDB, use the index, and are deterministic. The only migration cost is one schema change and one bulk update:

ALTER TABLE access_log MODIFY client_ip INT UNSIGNED NOT NULL;
UPDATE access_log SET client_ip = INET_ATON(client_ip); -- careful: needs a plan for live tables
Enter fullscreen mode Exit fullscreen mode

(For live tables, do it in a staged fashion: add a new column, backfill in batches, swap, drop the old one.)

What about PostgreSQL, Python, and IPv6?

  • PostgreSQL has first-class INET and CIDR types that sort and index properly — use them, they beat both TEXT and naive integer storage.
  • Python: ipaddress module handles conversions cleanly: 
import ipaddress

ip = ipaddress.ip_address('192.168.1.1')
print(int(ip))          # 3232235777
print(ipaddress.ip_address(3232235777))  # 192.168.1.1

# Whole subnet bounds as integers — trivial to turn into a BETWEEN query
net = ipaddress.ip_network('192.168.1.0/24')
print(int(net.network_address), int(net.broadcast_address))
Enter fullscreen mode Exit fullscreen mode
  • IPv6: 128 bits, so the string-vs-int tradeoff is even worse. Store as BINARY(16) (or a DECIMAL(39,0) / VARBINARY in engines without one) — never as 39-character text if you can avoid it.

When you actually need the dotted form

Application logs and debugging always want 192.168.1.1. Conversion at read time is cheap: INET_NTOA on the way out costs microseconds per row, far less than the string storage costs you on every write and every range scan.

If you are doing this conversion by hand — subnet to bounds, or bounds back to dotted decimal — the IP Address Converter on IPCalcPlus converts IPv4 addresses between dotted decimal, binary, hex, octal, and integer formats and expands a CIDR block to its integer range. That is the exact arithmetic you would otherwise do twice for each subnet in a migration script. No data leaves the browser, so you can throw your real production ranges at it.

A checklist for your next migration

  1. Identify every VARCHAR column that stores an IP (logs, user sessions, rate-limit tables, firewall rules).
  2. Pick the type: MySQL/MariaDB → INT UNSIGNED; PostgreSQL → INET; IPv6 needs → BINARY(16).
  3. Rewrite all queries touching that column to use the integer form — BETWEEN on the index instead of LIKE '10.0.%' or string comparison.
  4. Backfill with staged updates; measure EXPLAIN before/after on the hottest query.
  5. Never let an ORM sneak a string back in — cast at the boundary, not in the query.

The 15 minutes this migration takes will pay for itself the first time a range filter returns the right rows — fast. Integer IP storage is the kind of change that looks like a micro-optimization and behaves like a bugfix: it fixes correctness (string ordering) and performance (index scan) at the same time.

Top comments (0)