Planning on storing IP addresses in MYSQL or any other similar database for your PHP application? If you are like most, you probably were thinking of storing the IP addresses as strings in a varchar(15) row. Well, there actually is a much faster and more efficient way of storing IP addresses. The secret lies in PHP’s ip2long() and long2ip() functions which convert a dotted IP address to and from it’s long integer representation. The IP address can then be stored in the database as an int(11) format. When you need to work with the IP address later, simply convert back to the dotted format using the long2ip() function.
The long format is much faster to query for in MYSQL because you are searching an integer index as opposed to a string index. Make sure you don’t use “like” in your query or you could lose this speed increase, and make sure you include an index on this column.
Example code in PHP:
$ip_address="212.253.52.1"; $converted_address=ip2long($ip_address); $sql="SELECT FROM ip_table WHERE ip = $converted_address"; $mysql_query($sql);
In addition to a query speed increase, storing IP addresses as int(11) also has a disk space savings. In my test with 50,000 unique IP addresses, storing them as int(11) used 40% less disk space for the combined data and index than the varchar(15) format. Take note!
