MySQL store IP as int for higher performance?
database, mysql, php
Solution
While I think this is micro optimization you can use the function `ip2long()` to convert an ascii representation of an IP address to a 32bit integer and `long2ip()` to convert it back.
Try it:
$int = ip2long('192.168.0.1');
echo $int; // 3232235521
$ip = long2ip(3232235521);
echo $ip; // 192.168.0.1
Btw, the conversion between a string representation of an IP and it's integer representation works not like you expect. You see that 192.168.0.1 is converts not to 19216801. Look at it, it is obvious that this couldn't be reconverted. Is it 19.216.80.1 or 192.168.0.1?
An IP address is a 32bit integer which is stored in binary format (like any number). Lets have a look at the binary representation of `3232235521` `(192.168.0.1)`:
11000000 10101000 00000000 00000001
I've split into 8bit units (bytes) for better readability. This is what the computer sees. But this is hard to remember for humans.
The ascii representation from that will be build by convert the value of every byte to decimal and separate them by a `.`:
11000000 => 192
10101000 => 168
00000000 => 0
00000001 => 1
.. what gives you `192.168.0.1`, what is more easy to remember by humans. (Beside some other advantages regarding the readability of subnet groups)
Problem
I have a database with a lot of queries per second. The query search for the IP address value. So is it reasonable to store IPs like for example `34.549.53.23` as int value: 345495323? Will the queries be faster? I know for example that `192.168.1.1` and `192.16.81.1` are both stored as 19216811 but this does not matter.