IP Address Database Storage

[MicroBerto]

Hi all,

We have seen some issues with some database tables (mainly pageviews) getting too large and out of control. Some fixes have been made and helped, but I'd like to go further and discuss IP Address Storage.

Currently, Pligg uses VARCHARs to store IP address. This means that, at worst, each IP address is going to store 15 bytes of data (123.567.901.345 if you count numbers and dots). This adds up to a LOT of wasted space when we log every single profile/story/out pageview.

What is often done instead of this is to use INT UNSIGNED, and use built-in functions to do conversions to integer for us. This takes up 4 bytes -- significant savings.

The functions:

1. INET_ATON()
   
   Let's say we get an IP Address: $ip_address = $_SERVER['REMOTE_ADDR'];
   
   When submitting this into the database, what you need to do is this:
   
   
   PHP Code:

   $query = "INSERT INTO votes (vote_ip) VALUES (INET_ATON('$ip_address'))"; 

   And then the string IP gets formatted into UINT
   
2. INET_NTOA()
   
   Now we need to get it out.
   
   
   PHP Code:

   $query = "SELECT INET_NTOA(pv_user_ip) FROM pageviews WHERE pv_type = 'profile' DESC LIMIT 1"; // (or whatever...)
$result = $db->query($query); // or however you do your queries 

   And now $result will have the text format back to you.

Files that would need to be changed:

1. live2.php
2. out.php
3. register.php
4. possibly story.php and user.php (they don't seem to be writing to the DB, just checking an IP)
5. install/installtables.php
6. install/upgrade.php (this might take some functions to run through all stored IP addresses, get them into an array, change the table, reformat the IP, and store them back in)
7. libs/votes.php
8. libs/pageview.php

References:

1. MySQL Misc Functions
2. Arjen's Journal - Storing IP Addresses

At a savings of up to 11 bytes per every vote and every time someone clicks on something, I think this is worth the work.

Let me know what you think, I'm willing to help with most of these.

Cheers,
berto

[kbeeveer46]

Thanks for this. You made it easy for the devs to make the changes needed. Now if all the threads were like this :P

[acidride]

Also, turn the MySQL cache off for the pageview table. This is very important from a performance standpoint.

[dollars5]

Thanks m8, saving 4bytes per record is a great thing as this is a table that will get growing real fast. I hope this has already made its way to the SVN code.

[dennis]

Is there any plan to migrate the dB tables to use unsigned int(4)? I've prep'd a regEx that will convert the dB -- so that part is covered..

[netwb]

Originally Posted by MicroBerto

Hi all,

We have seen some issues with some database tables (mainly pageviews) getting too large and out of control. Some fixes have been made and helped, but I'd like to go further and discuss IP Address Storage.

Currently, Pligg uses VARCHARs to store IP address. This means that, at worst, each IP address is going to store 15 bytes of data (123.567.901.345 if you count numbers and dots). This adds up to a LOT of wasted space when we log every single profile/story/out pageview.

What is often done instead of this is to use INT UNSIGNED, and use built-in functions to do conversions to integer for us. This takes up 4 bytes -- significant savings.

The functions:

1. INET_ATON()
   
   Let's say we get an IP Address: $ip_address = $_SERVER['REMOTE_ADDR'];
   
   When submitting this into the database, what you need to do is this:
   
   
   PHP Code:

   $query = "INSERT INTO votes (vote_ip) VALUES (INET_ATON('$ip_address'))"; 

   And then the string IP gets formatted into UINT
   
2. INET_NTOA()
   
   Now we need to get it out.
   
   
   PHP Code:

   $query = "SELECT INET_NTOA(pv_user_ip) FROM pageviews WHERE pv_type = 'profile' DESC LIMIT 1"; // (or whatever...)
$result = $db->query($query); // or however you do your queries 

   And now $result will have the text format back to you.

Files that would need to be changed:

1. live2.php
2. out.php
3. register.php
4. possibly story.php and user.php (they don't seem to be writing to the DB, just checking an IP)
5. install/installtables.php
6. install/upgrade.php (this might take some functions to run through all stored IP addresses, get them into an array, change the table, reformat the IP, and store them back in)
7. libs/votes.php
8. libs/pageview.php

References:

1. MySQL Misc Functions
2. Arjen's Journal - Storing IP Addresses

At a savings of up to 11 bytes per every vote and every time someone clicks on something, I think this is worth the work.

Let me know what you think, I'm willing to help with most of these.

Cheers,
berto

THANKS FOR THE solution but the THE IP adress is enable of the database to the register users? yes of no?
thanks