Why Storing IP Addresses as VARCHAR(15) Is Suboptimal – Use INT UNSIGNED Instead

During a MySQL interview, candidates often answer that an IP address should be stored in a VARCHAR(15) column because the dotted‑decimal representation varies from 7 to 15 characters. This answer sounds plausible but is not the most efficient solution.

The essence of an IPv4 address is a 32‑bit unsigned integer. The human‑readable form like 192.168.0.1 is merely a convenient notation; its numeric value is 3232235521. Therefore, the correct storage type is an unsigned integer rather than a string.

MySQL supports signed and unsigned integer types. The relevant ranges are:

TINYINT : signed -128 ~ 127, unsigned 0 ~ 255

SMALLINT : signed -32768 ~ 32767, unsigned 0 ~ 65535

MEDIUMINT : signed -8388608 ~ 8388607, unsigned 0 ~ 16777215

INT : signed -2147483648 ~ 2147483647, unsigned 0 ~ 4294967295

BIGINT : signed -9223372036854775808 ~ 9223372036854775807, unsigned 0 ~ 18446744073709551615

Since an IPv4 address fits within the range of an INT UNSIGNED, it can be stored in a 4‑byte column, whereas VARCHAR(15) occupies up to 16 bytes (including length byte). The differences are:

Storage space : INT UNSIGNED uses 4 bytes, while VARCHAR(15) typically uses 16 bytes.

Retrieval speed : Indexes on integer columns are far faster than on string columns because string comparison must examine characters sequentially.

MySQL provides built‑in conversion functions INET_ATON() (dotted‑decimal to integer) and INET_NTOA() (integer to dotted‑decimal). These functions use fast binary shift operations, but for high‑throughput applications it is often better to perform the conversion in the application layer to reduce database load.

In summary, store IPv4 addresses as INT UNSIGNED for optimal space usage and query performance, and use INET_ATON / INET_NTOA or application‑side conversion when needed.