Match an IPv4 Address Against a CIDR Subnet in MySQL

At some point, someone asks the same question of every log table: "how many of these requests came from 192.168.1.0/24?" If the addresses are stored as text, the usual answer is LIKE '192.168.1.%'. This only works for subnets that end on an octet boundary, and it cannot answer anything about a /23 or a /27. Stored as numbers, the same question becomes a simple range check that an index can answer in milliseconds.

This post shows the three ways to test CIDR membership in MySQL and MariaDB, and which of them can use an index. It also gives a small set of stored functions to keep the queries readable. Everything below was run on MariaDB 11.8. The SQL uses nothing MariaDB-specific apart from the test-data generator noted below. So it should run on MySQL too, but I have not tested it there.

Store addresses as INT UNSIGNED

An IPv4 address is a 32-bit number, and INT UNSIGNED holds exactly 0 to 4294967295. INET_ATON() converts the dotted form to that number and INET_NTOA() converts it back. This uses four bytes per row instead of up to 15 characters. More importantly, the values sort in network order: 10.0.0.5 comes before 192.168.1.10. This is not true of the strings '10.0.0.5' and '9.9.9.9'.

CREATE TABLE access_log (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip   INT UNSIGNED NOT NULL,
  seen DATETIME NOT NULL,
  KEY idx_ip (ip)
);
INSERT INTO access_log (ip, seen) VALUES
  (INET_ATON('192.168.1.10'),  '2026-10-01 08:00:00'),
  (INET_ATON('192.168.1.200'), '2026-10-01 08:05:00'),
  (INET_ATON('192.168.2.7'),   '2026-10-01 08:10:00'),
  (INET_ATON('10.0.0.5'),      '2026-10-01 08:15:00'),
  (INET_ATON('203.0.113.9'),   '2026-10-01 08:20:00');
SELECT id, ip, INET_NTOA(ip) AS addr FROM access_log;

+----+------------+---------------+
| id | ip         | addr          |
+----+------------+---------------+
|  4 |  167772165 | 10.0.0.5      |
|  1 | 3232235786 | 192.168.1.10  |
|  2 | 3232235976 | 192.168.1.200 |
|  3 | 3232236039 | 192.168.2.7   |
|  5 | 3405803785 | 203.0.113.9   |
+----+------------+---------------+

The rows came back in address order, not insert order, because the optimiser read them straight from the idx_ip index. That ordering is what makes the fast query below possible.

Note: do not use a signed INT. Everything from 128.0.0.0 upwards is larger than 2147483647. It will be rejected in strict mode, or silently changed to the largest allowed value without strict mode.

Method 1: (ip & mask) = network

This is the standard test and the one routers use. AND the address with the netmask and compare the result with the network address:

SELECT INET_NTOA(ip) AS addr FROM access_log
WHERE (ip & INET_ATON('255.255.255.0')) = INET_ATON('192.168.1.0');

+---------------+
| addr          |
+---------------+
| 192.168.1.10  |
| 192.168.1.200 |
+---------------+

For a prefix length instead of a dotted mask, build the mask by shifting 32 one-bits left by the number of host bits. The extra & 0xFFFFFFFF is needed because MySQL does bit arithmetic in 64 bits. Here is a /23, which covers 192.168.0.0 to 192.168.1.255:

SELECT INET_NTOA(ip) AS addr FROM access_log
WHERE ip & (0xFFFFFFFF << (32 - 23) & 0xFFFFFFFF) = INET_ATON('192.168.0.0');

+---------------+
| addr          |
+---------------+
| 192.168.1.10  |
| 192.168.1.200 |
+---------------+

It is correct and short, and fine for a single query that you run once. The problem is that the column is wrapped in an expression. The optimiser cannot turn ip & mask = x into a range on the index, so it has to look at every row.

Method 2: BETWEEN network AND broadcast

A CIDR block is a contiguous run of addresses: from the network address to the broadcast address. So the same question can be asked as a plain range on the bare column:

SELECT INET_NTOA(ip) AS addr FROM access_log
WHERE ip BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');

+---------------+
| addr          |
+---------------+
| 192.168.1.10  |
| 192.168.1.200 |
+---------------+

Same answer, but now ip stands alone on the left, and both bounds are constants. To see the difference, I filled the table with 200,000 random addresses plus 1,000 inside 192.168.1.0/24 (using MariaDB's seq_1_to_200000 sequence table. On MySQL use a recursive CTE). Here is EXPLAIN for both forms, reduced to the useful columns:

EXPLAIN SELECT COUNT(*) FROM access_log
WHERE (ip & 0xFFFFFF00) = INET_ATON('192.168.1.0');
EXPLAIN SELECT COUNT(*) FROM access_log
WHERE ip BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');

+-------+---------------+--------+--------+--------------------------+
| type  | possible_keys | key    | rows   | Extra                    |
+-------+---------------+--------+--------+--------------------------+
| index | NULL          | idx_ip | 200994 | Using where; Using index |
+-------+---------------+--------+--------+--------------------------+
+-------+---------------+--------+--------+--------------------------+
| type  | possible_keys | key    | rows   | Extra                    |
+-------+---------------+--------+--------+--------------------------+
| range | idx_ip        | idx_ip | 1002   | Using where; Using index |
+-------+---------------+--------+--------+--------------------------+

The bitmask version is type index: a full read of all 200,000 index entries. The BETWEEN version is type range and touches only the 1,002 matching entries. Both return the same count of 1002.

A reusable stored function

Writing INET_ATON() twice with hand-calculated bounds soon becomes tiresome. These three functions take a CIDR string such as '192.168.1.0/24' and do the arithmetic. A bare address without a prefix is treated as /32.

DELIMITER //
-- cidr_first() -- first address (network) of an IPv4 CIDR block
CREATE FUNCTION cidr_first(cidr VARCHAR(18))
RETURNS INT UNSIGNED
DETERMINISTIC NO SQL
BEGIN
  DECLARE net BIGINT UNSIGNED;
  DECLARE len TINYINT UNSIGNED DEFAULT 32;
  IF cidr NOT REGEXP '^[0-9.]+(/[0-9]{1,2})?$' THEN
    RETURN NULL;
  END IF;
  IF LOCATE('/', cidr) > 0 THEN
    SET len = SUBSTRING_INDEX(cidr, '/', -1);
  END IF;
  SET net = INET_ATON(SUBSTRING_INDEX(cidr, '/', 1));
  IF net IS NULL OR len > 32 THEN
    RETURN NULL;
  END IF;
  RETURN net & ((0xFFFFFFFF << (32 - len)) & 0xFFFFFFFF);
END//
-- cidr_last() -- last address (broadcast) of an IPv4 CIDR block
CREATE FUNCTION cidr_last(cidr VARCHAR(18))
RETURNS INT UNSIGNED
DETERMINISTIC NO SQL
BEGIN
  DECLARE net INT UNSIGNED DEFAULT cidr_first(cidr);
  DECLARE len TINYINT UNSIGNED DEFAULT 32;
  IF net IS NULL THEN
    RETURN NULL;              -- cidr_first() has already validated cidr
  END IF;
  IF LOCATE('/', cidr) > 0 THEN
    SET len = SUBSTRING_INDEX(cidr, '/', -1);
  END IF;
  RETURN net | (0xFFFFFFFF >> len);
END//
-- ip_in_cidr() -- 1 if ip (INT UNSIGNED) is inside cidr, 0 if not
CREATE FUNCTION ip_in_cidr(ip INT UNSIGNED, cidr VARCHAR(18))
RETURNS TINYINT
DETERMINISTIC NO SQL
RETURN ip BETWEEN cidr_first(cidr) AND cidr_last(cidr)//
DELIMITER ;

The REGEXP check is there for a practical reason. Without it, a value like '10.0.0.0/abc' reaches the SET len = ... line. In strict mode, this stops the whole query with ERROR 1366 Incorrect integer value. With it, bad input just returns NULL. cidr_first() also masks off any host bits, so 192.168.1.77/24 is understood as 192.168.1.0/24.

SELECT c, INET_NTOA(cidr_first(c)) AS first, INET_NTOA(cidr_last(c)) AS last
FROM (SELECT '192.168.1.77/24' c UNION ALL SELECT '10.0.0.0/8'
      UNION ALL SELECT '172.16.5.4/30' UNION ALL SELECT '203.0.113.9'
      UNION ALL SELECT '0.0.0.0/0' UNION ALL SELECT '10.0.0.0/33'
      UNION ALL SELECT '10.0.0.0/abc' UNION ALL SELECT '10.0.0.0/') t;

+-----------------+-------------+-----------------+
| c               | first       | last            |
+-----------------+-------------+-----------------+
| 192.168.1.77/24 | 192.168.1.0 | 192.168.1.255   |
| 10.0.0.0/8      | 10.0.0.0    | 10.255.255.255  |
| 172.16.5.4/30   | 172.16.5.4  | 172.16.5.7      |
| 203.0.113.9     | 203.0.113.9 | 203.0.113.9     |
| 0.0.0.0/0       | 0.0.0.0     | 255.255.255.255 |
| 10.0.0.0/33     | NULL        | NULL            |
| 10.0.0.0/abc    | NULL        | NULL            |
| 10.0.0.0/       | NULL        | NULL            |
+-----------------+-------------+-----------------+

SELECT ip_in_cidr(INET_ATON('192.168.1.200'), '192.168.1.0/24') AS a,
       ip_in_cidr(INET_ATON('192.168.2.7'),  '192.168.1.0/24') AS b,
       ip_in_cidr(INET_ATON('192.168.1.7'),  '192.168.0.0/23') AS c,
       ip_in_cidr(INET_ATON('192.168.1.7'),  'bogus') AS d;

+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    1 |    0 |    1 | NULL |
+------+------+------+------+

Use the function for the bounds, not the row

ip_in_cidr() is convenient for checking a single address, but it is the wrong thing to put in a WHERE clause over a big table. It hides the column inside a function call, which causes a full scan again. It also runs two stored functions (with a regular expression) for every row. On the 201,005-row test table, I timed the three forms with the client's -vvv timer, three runs each. I used one machine (a MariaDB 11.8 container on my workstation):

The last form calls the functions only with constant arguments, so the server evaluates them once and gets a normal range scan. EXPLAIN confirms it: type range, key idx_ip, 1002 rows. The habit worth keeping is simple: the bare column on one side, constants on the other.

SELECT COUNT(*) FROM access_log
WHERE ip BETWEEN cidr_first('192.168.1.0/24') AND cidr_last('192.168.1.0/24');

+----------+
| COUNT(*) |
+----------+
|     1002 |
+----------+

Matching against a table of subnets

The same idea scales to a list of networks. Store the first and last address of each block once, when the row is written, and join on BETWEEN:

CREATE TABLE subnets (
  name     VARCHAR(30)  NOT NULL,
  cidr     VARCHAR(18)  NOT NULL,
  first_ip INT UNSIGNED NOT NULL,
  last_ip  INT UNSIGNED NOT NULL,
  KEY idx_range (first_ip, last_ip)
);
INSERT INTO subnets (name, cidr, first_ip, last_ip)
SELECT name, cidr, cidr_first(cidr), cidr_last(cidr)
FROM (SELECT 'office-lan' AS name, '192.168.1.0/24' AS cidr
      UNION ALL SELECT 'vpn',        '10.0.0.0/8'
      UNION ALL SELECT 'test-net-3', '203.0.113.0/24') AS t;
SELECT INET_NTOA(l.ip) AS addr, COALESCE(s.name, '-') AS subnet
FROM access_log AS l
LEFT JOIN subnets AS s ON l.ip BETWEEN s.first_ip AND s.last_ip
WHERE l.id <= 5
ORDER BY l.id;

+---------------+------------+
| addr          | subnet     |
+---------------+------------+
| 192.168.1.10  | office-lan |
| 192.168.1.200 | office-lan |
| 192.168.2.7   | -          |
| 10.0.0.5      | vpn        |
| 203.0.113.9   | test-net-3 |
+---------------+------------+

If the subnets overlap (a /8 and a /24 inside it), an address matches both, and the join returns two rows for it. Pick the most specific one with ORDER BY last_ip - first_ip and a limit, or keep the list non-overlapping.

Pitfalls

• IPv4 only. INT UNSIGNED and INET_ATON() cannot hold IPv6. For mixed data see the VARBINARY(16) approach in the related post below.
• Stored functions and binary logging. Creating these functions on a server with binary logging enabled needs SUPER or log_bin_trust_function_creators = 1. The ipcalc post shows the exact errors.
• NULL is not 0. ip_in_cidr() returns NULL for a malformed CIDR, and WHERE NULL matches nothing. This is usually what you want. Wrap it in COALESCE(..., 0) if you need a strict yes/no.
• Converting an existing VARCHAR column. Add the new column, fill it with UPDATE t SET ip_num = INET_ATON(ip_text), check for rows where the result is NULL, and only then drop the text column.

The range optimisation chapter of the MySQL manual explains in detail which WHERE shapes can use an index range.

Related posts

• ipcalc() in MySQL: network, broadcast and prefix from a netmask
• Storing IPv4 and IPv6 addresses with INET6_ATON()