Why Store IPv4 Addresses as UNSIGNED INT in MySQL? Space, Speed, and Queries Explained

When reading the third edition of High Performance MySQL (section 4.1.7), the author recommends storing IPv4 addresses as a 32‑bit UNSIGNED INT rather than as a string, but does not explain why. The following analysis clarifies the reasons.

Benefits of using an unsigned integer

Space savings: a VARCHAR(15) column requires up to 15 bytes plus one extra byte for length, whereas an UNSIGNED INT always occupies exactly 4 bytes.

Index efficiency: smaller column size means smaller index entries, which reduces both storage and I/O during index scans.

Range queries: numeric columns can be filtered with BETWEEN … AND more efficiently than string comparisons.

For example, an IPv4 address needs between 7 and 15 characters as text; storing it as VARCHAR(15) consumes 8‑16 bytes per row, while the integer representation consistently uses 4 bytes.

Alternative storage approaches

Splitting the address into four separate columns (one per octet) is possible, but typically offers no advantage in space or query performance compared with a single integer column.

Drawbacks

Human readability: integers are not immediately recognizable as IP addresses.

Conversion overhead: applications must translate between the numeric and dotted‑decimal forms.

Built‑in MySQL conversion functions

MySQL provides INET_ATON() to convert a dotted‑decimal string to an integer and INET_NTOA() to perform the reverse:

mysql> select inet_aton('192.168.0.1');
+--------------------------+
| inet_aton('192.168.0.1') |
+--------------------------+
|               3232235521 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(3232235521);
+-----------------------+
| inet_ntoa(3232235521) |
+-----------------------+
| 192.168.0.1           |
+-----------------------+
1 row in set (0.00 sec)

IPv6 considerations

For IPv6, storing the address in a VARBINARY(16) column yields similar space and index benefits, and MySQL offers INET6_ATON() and INET6_NTOA() for conversion.

Application‑level conversion example (Java)

package com.mikan;

/**
 * @author Mikan
 * @date 2015-09-22 10:59
 */
public class IpLongUtils {
    /** Convert dotted‑decimal IP string to long */
    public static long ip2Long(String ipStr) {
        String[] ip = ipStr.split("\\.");
        return (Long.valueOf(ip[0]) << 24) + (Long.valueOf(ip[1]) << 16)
                + (Long.valueOf(ip[2]) << 8) + Long.valueOf(ip[3]);
    }

    /** Convert long back to dotted‑decimal string */
    public static String long2Ip(long ipLong) {
        StringBuilder ip = new StringBuilder();
        ip.append(ipLong >>> 24).append('.');
        ip.append((ipLong >>> 16) & 0xFF).append('.');
        ip.append((ipLong >>> 8) & 0xFF).append('.');
        ip.append(ipLong & 0xFF);
        return ip.toString();
    }

    public static void main(String[] args) {
        System.out.println(ip2Long("192.168.0.1"));
        System.out.println(long2Ip(3232235521L));
        System.out.println(ip2Long("10.0.0.1"));
    }
}

Running the program prints:

3232235521
192.168.0.1
167772161

Further reading

https://bafford.com/2009/03/09/mysql-performance-benefits-of-storing-integer-ip-addresses/

This external benchmark confirms the space and speed advantages described above.