Most efficient way to store IP Address in MySQL

Question

What is the most efficient way to store and retrieve IP addresses in MySQL? Right now I'm doing:

SELECT * FROM logins WHERE ip = '1.2.3.4'

Where ip is a VARCHAR(15) field.

Is there a better way to do this?

Answer 1

For IPv4 addresses, you may want to store them as an int unsigned and use the INET_ATON() and INET_NTOA() functions to return the IP address from its numeric value, and vice versa.

Example:

SELECT INET_ATON('127.0.0.1');

+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 | 
+------------------------+
1 row in set (0.00 sec)


SELECT INET_NTOA('2130706433');

+-------------------------+
| INET_NTOA('2130706433') |
+-------------------------+
| 127.0.0.1               | 
+-------------------------+
1 row in set (0.02 sec)