In this article, I’ll explain the main advantage of storing IP addresses using the integer data type instead of the traditional CHAR/VARCHAR. This approach can also serve as a good example for optimizing other fields or columns.
To help you understand better, let’s create a simple table that stores an IP address using the CHAR type.
Note that the true goal of this article is to highlight the improper or inadequate use of data types in database design.
mysql> CREATE DATABASE example;
mysql> USE example;
mysql> CREATE TABLE log
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user CHAR(30),
visits INT,
ip CHAR(15)
);
Let’s insert some records into this table.
mysql> INSERT INTO log VALUES(null, ‘douglas’, 10, ‘192.168.10.10’);
mysql> INSERT INTO log VALUES(null, ‘luciana’, 10, ‘200.213.162.161’);
mysqll>INSERT INTO log VALUES(null, ‘alberto’, 10, ‘213.187.0.1’);
Well, so far we have three records in our table, and we will start our first analysis.
What is the size occupied by the IP column, and what does it effectively cost?
Using the LENGTH function, we can determine the space (in bytes) used by each digit/character based on the CHARSET:
mysql> SELECT LENGTH(IP) FROM log;
+—————–+
| LENGTH(IP) |
+—————–+
| 13 |
| 15 |
| 11 |
+—————–+
3 rows in set (0.05 sec)
The total cost for each column is 15 bytes because we are using CHAR, which always pads with spaces on the right to fill the entire column length. If it were VARCHAR, the cost would be 13+1 bytes, 15+1 bytes, and 11+1 bytes respectively, since VARCHAR costs the number of input characters plus 1 byte.
Storing IP addresses as string types results in higher storage costs on disk and in memory. So, how can we save space by using integers? The answer lies in the way integers are stored and their storage efficiency!
In MySQL/MariaDB, we can use two functions for this kind of operation: INET_ATON and INET_NTOA. The first converts the IP address into an integer, and the second performs the reverse operation.
mysql> SELECT INET_ATON(‘192.168.0.1’);
+————————————-+
| INET_ATON(‘192.168.0.1’) |
+————————————-+
| 3232235521 |
+————————————-+
1 row in set (0.00 sec)
mysql> SELECT INET_NTOA(3232235521);
+————————————+
| INET_NTOA(3232235521) |
+————————————+
| 192.168.0.1 |
+———————————–+
1 row in set (0.00 sec)
Memory/Disk Savings:
We created two tables: the first as shown earlier, with the IP field as CHAR, and the second with the IP field as INT. Both tables used the MARIA storage engine (or MyISAM, if preferred). I inserted 500,000 rows into each. Let’s take a look at the numbers I obtained:
Table with IP field as CHAR: data_size = 31.87 MB
Table with IP field as INT: data_size = 16.17 MB
The disk space savings were 51% by using the IP field as an INT.
And what about performance? Did it improve?
Let’s run a very simple query performing a count(*) on both tables.
mysql> SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM log ORDER BY IP DESC));
+——————————————————————————————————+
| BENCHMARK(1000000, (SELECT COUNT(*) FROM log ORDER BY IP DESC)) |
+——————————————————————————————————+
| 0 |
+——————————————————————————————————+
1 row in set (0.27 sec)
mysql> SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM log2 ORDER BY IP DESC));
+——————————————————————————————————–+
| BENCHMARK(1000000, (SELECT COUNT(*) FROM log2 ORDER BY IP DESC)) |
+——————————————————————————————————–+
| 0 |
+——————————————————————————————————–+
1 row in set (0.15 sec)
Well, I think this already gives us an idea of how choosing the right data type can greatly influence both the performance and the disk/memory usage of our database.
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
