MySQL store IP as int for higher performance? [duplicate]

Question

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.

Answer 1

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)

Answer 2

Yes the queries will be faster. Also to save the IP as integer use the mysql's INET_ATON() and to get back the IP address from integer use INET_NTOA() . Pleas refer this page for more info

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html

If you are trying with PHP you could use ip2long() and long2ip() viceversa

Refer http://www.php.net/manual/en/function.ip2long.php