Tuesday, August 9, 2016

Storing and Retrieving IP Address in MySQL DB

MySQL Database allows to store IP Address as INT data type and retrieves the IP Address from INT data type.

Need: An INT takes 4 bytes to store, where as VARCHAR of utf8 charset takes 1 byte for each character. So to store below IP Address it will take approx 15 bytes (15 char).

Wondering, so much effort to save just 11 bytes. Now, take some 1 billion IP Addresses into consideration. It will end up saving some 10GB of data. Not only saving disk space, but less data can reside in memory thus making SELECT query pretty fast.

Example:
mysql> select inet_aton('102.132.145.132');
+------------------------------+
| inet_aton('102.132.145.132') |
+------------------------------+
|                   1719964036 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(1719964036);
+-----------------------+
| inet_ntoa(1719964036) |
+-----------------------+
| 102.132.145.132       |
+-----------------------+
1 row in set (0.00 sec)

Note: Take the column datatype as UNSIGNED INT.
UNSIGNED INT has range from 0 to 4294967295

mysql> select inet_aton('255.255.255.255');
+------------------------------+
| inet_aton('255.255.255.255') |
+------------------------------+
|                   4294967295 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select inet_aton('0.0.0.0');
+----------------------+
| inet_aton('0.0.0.0') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

No comments:

Post a Comment