This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing about the effective storage of IP-Address into Database System.
Now let’s talk about four part of IP-Address.
E.g, 152.145.10.25
Many database developers or database designer store this IP-Address into one column which is VARCHAR(15).
What do you think? Is this effective storage of IP-Address?.
Now imagine that you are playing with 10,0,00,00 online customers in which all this IP-Address of the customer requires for analysis.
If we stored all IP-Addresses into one column, you have to perform string manipulation for finding a particular range of IP-Address.
The string operation is always costly and not advisable.
Effective storage of IP-Address is very simple:
Just divide your full-length IP-Address into four part.
E.g.
Full IP-Address: 152.145.10.25
Four Part :
152
145
10
25
Now create four tinyint/smallint column to store this four different part.
column like,
ip1 tinyint
ip2 tinyint
ip3 tinyint
ip4 tinyint
The full IP-Address split into four different integer part.
Now the task is very easy:
You can also apply an index on all this part and perform range analysis very quickly.
Yes, I agree that during insertion you also require splitting for storing in the different columns, but this is only a one time process after that you can easily use different parts for various analysis.
During select, you can also concat this four part and make full IP-Address for display purpose.
