PHP: Proper way to store IP in MySql and quickest way to search for IP throughout millions of rows.

Question

I'm storing IPv4 addresses in a "int unsigned" column type with inet_aton. [Am I doing this right? And is using "unsigned" necessary?] This particular column is indexed as well. Since there will be millions of rows and multiple rows containing the same IP throughout the entire table what will be the fastest way to search for these rows?

..or am I going about this the wrong way?

Answer 1

Am I doing this right? And is using "unsigned" necessary?

Yes. Without unsigned higher ip addresses will not be stored properly and using int (instead of varchar) stores it most efficiently.

what will be the fastest way to search for these rows?

As far as search optimization, that depends on what all you're searching for (additional tables, etc...). In general, indexing an unsigned int column gives you fast performance.

Answer 2

Using the inet_aton is the right way of doing this, so you're not storing extra meaningless info (no point in being able to store a value greater than 256 for any given 3 numbers). It results in a 32 bit number, which will fit into the unsigned int.

Indexing on the int column will make lookups by IP address quick. If your database gets REALLY big, you'll start running into scaling problems storing this sort of thing in MySQL.

I'll assume you're not going to do that, but will point out that storing complete log info for a large busy site in an RDBMS is generally agreed to be a Bad Thing(tm). You don't need the relational integrity properties the database guarantees, and you write many more entries than you read. Consider nosql, or appending to flat files instead, and parsing your logs when necessary using a dedicated program.

Answer 3

Yes, this is the best way to store IP addresses in MySQL.

If you look at the documentation for INET_ATON you can see that it is recommended to use an UNSIGNED INT column or any IP address with the first octed over 127 will not be stored correctly.

This is also a very fast way to do searches. MySQL handles integer columns very well, and by indexing this column and using INET_ATON in your search, you can achieve very fast queries.