SQL structure for storing an IP address

[d999]

Hi i'm making a registration form that will store users by IP: or more specifically

 

$user_ip = $_SERVER[‘REMOTE_ADDR’];

 

The value of user IP will then be store in a table and a field name remote_addr.

 

What structure of that field be in sql to store an IP correctly? i.e. VARCHAR, long INT ????????? and what size etc

[corbin]

The smallest it will fit into is an integer (unsigned to be specific, although it would technically fit in an integer).

 

 

If you want to store it as an integer, you would need to use INET_ATON.

 

 

Advantages/disadvantages of storing it as a varchar or uint:

 

Varchar - Advantages:

-Doesn't need to be converted to display/search

Varchar - Disadvantages:

-15 bytes

-Bigger index than an integer column.

 

int - advs:

-Smaller.  4 bytes.

-Smaller index than varchar column.

-Ability to do numeric ranges.  (You could do ranges with varchar too, but it would involve a LIKE clause)

 

int - disadvs:

-Must be converted before display readably

 

 

I'm sure there are other advantages/disadvantages.  Those are just the first ones that come to mind.

[aschk]

You will want to store the IP in BOTH forms. i.e. as a VARCHAR for readable/printable purposes and an INT so you can do numeric calculations using it. You're denormalising your data a little but using this structure you don't have to perform conversions on the INT version to get the printable version. This works well if you're doing a lookup on a range (i.e. between x < y) and you want all the readable addresses.

 

structure:

ip_numeric | ip_printable
INT          VARCHAR(15)

[fenway]

Why store both? Just use INET_NTOA() to get the "printable" version... don't store IPv4 as VARCHAR.

[aschk]

Ah good question fenway, here's my reason:

 

When you do a search for a range (using the integer conversion value), do you really want to have to get MySQL to convert EVERY row back into a printable format for your resultset? Nope, you don't... not every time you do a call. Do it once (on insert) and have the printable version in there saving you valuable MySQL processing time. Yes it's slightly denormalised but this isn't an issue.

 

n.b. It also means if you're just scanning table data you can easily see which addresses you're looking at.

[corbin]

Hrmmm, I'm semi-interested in this, so I'm going to do a few benchmarks in a second lol.

 

 

 

I suspect that converting from int to char probably isn't very resource intensive unless the result set is huge.  When would the result set ever be huge?

 

 

Let's say you're pulling 100 rows at a time, the overhead would be fairly small for a simple function call for every row, yes?

 

 

Now let's say that same table has 100,000 rows....  1,500,000 bytes, which is only 1.43MB, but don't forget that the index would have to be rebuilt (or atleast a branch) on every single update issued.

 

 

 

 

So, I guess it would really depend on how the database is being accessed.

 

 

For something like a stat counter, for example, I would only store the int version.  For something where IP addresses are displayed often, I would probably store both (but I don't know when IPs would be displayed commonly x.x).

[fenway]

Ah good question fenway, here's my reason:

 

When you do a search for a range (using the integer conversion value), do you really want to have to get MySQL to convert EVERY row back into a printable format for your resultset? Nope, you don't... not every time you do a call. Do it once (on insert) and have the printable version in there saving you valuable MySQL processing time. Yes it's slightly denormalised but this isn't an issue.

 

n.b. It also means if you're just scanning table data you can easily see which addresses you're looking at.

I don't get it... how many IP addresses will you ever be showing to the user?

[aschk]

I don't know, but then I'm not the one requesting the answer to the question, so I covered all bases