Best way to store single IPs, IP ranges, IP blocks and IP classes and search quickly through them

Question

What is the best way to store in a MySQL database multiple IP types like:
- Single IP (123.123.123.123)
- IP Ranges (123.123.123.1 - 123.123.123.121)
- IP blocks (123.123.123.1/20 )
- IP Classes (123.123.123.* or 123.123..)

I was thinking to transform all ranges/blocks/classes into single IP's and store them with ip2long for a faster search into the table, but this will result in a 1 million+ database also i need from time to time to reduce/enlarge the classes or to change/delete the IP blocks.

This database will be accessed every time someone access my website (so it needs to be quick). Any ideas?

Answer 1

This tutorial can help: http://daipratt.co.uk/mysql-store-ip-address/

The most efficient say of saving IPv4 addresses is with an INT field (not VARCHAR as you might expect). You convert them using PHP's ip2long and back using either MySQL's INET_NTOA function or PHP's longtoip.

Source: IP address storing in MySQL database using PHP