The most efficient way to store IP addresses in MySQL

Question

What is the most efficient way to store and retrieve IP addresses in MySQL? Now I am doing:

SELECT * FROM logins WHERE ip = '1.2.3.4' 

Where ip is the VARCHAR(15) field VARCHAR(15) .

Is there a better way to do this?

Answer 1

For IPv4 addresses , you can save them as int unsigned and use INET_ATON() and INET_NTOA() 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) 

Answer 2

If you want to store IPv4 addresses, you can save them in a 32-bit integer field.

If you want to also support IPv6, then the string is probably the easiest to read / used way (although you can technically save them in a 16-byte VARBINARY() field, it would be very unpleasant to try to generate SQL statements to select by IP address "manually")

Answer 3

The most important thing is to make sure that the column is indexed. This can be of great importance for IP address based queries.

Answer 4

Is it possible to store an integer value directly in an integer field? An IP address is basically 4 shorts.

Check this out: http://en.kioskea.net/faq/945-converting-a-32-bit-integer-into-ip

Answer 5

Whatever is easier for you to work. The issue of size or speed is not a problem until you know that it is a problem with profiling. In some cases, it may be easier to work with a chain to simplify working with a chain if you need to perform partial matching. But, as a matter of space or performance, don't worry about it unless you have a real reason to worry about it.