Find method to store an IP in MySQL for better performance

by

I would suggest looking at what type of queries you will be running to decide which format you adopt.

Only if you need to pull out or compare individual octets would you have to consider splitting them up into separate fields.

Otherwise, store it as a 4 byte integer. That also has the bonus of allowing you to use the MySQL built-in INET_ATON() and INET_NTOA() functions.

Performance vs. Space

Storage:

If you are only going to support IPv4 addresses then your datatype in MySQL can be an UNSIGNED INT which only uses 4 bytes of storage.

To store the individual octets you would only need to use UNSIGNED TINYINT datatypes, not SMALLINTS, which would use up 1 byte each of storage.

Both methods would use similar storage with perhaps slightly more for separate fields for some overhead.

More info:

Performance:

Using a single field will yield much better performance, it’s a single comparison instead of 4. You mentioned that you will only run queries against the whole IP address, so there should be no need to keep the octets separate. Using the INET_* functions of MySQL will do the conversion between the text and integer representations once for the comparison.

More Related Contents:

  1. Most efficient way to store IP Address in MySQL [duplicate]
  2. SQL select only rows with max value on a column [duplicate]
  3. What is the error “Every derived table must have its own alias” in MySQL?
  4. Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
  5. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  6. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  7. How to grant remote access permissions to mysql server for user?
  8. Generating a series of dates [duplicate]
  9. How to escape single quotes in MySQL
  10. MySQL comparison with null value
  11. Unique constraint that allows empty values in MySQL
  12. Matching all values in IN clause
  13. INTERSECT in MySQL
  14. Having both a Created and Last Updated timestamp columns in MySQL 4.0
  15. MySQL select with CONCAT condition
  16. mysql field name from variable
  17. Multiple select statements in Single query
  18. Which is faster — INSTR or LIKE?
  19. Remove duplicate rows leaving oldest row Only?
  20. Remove special characters from a database field
  21. MySQL IN condition limit
  22. Set user variable from result of query
  23. Mysql command not found in OS X 10.7
  24. Reset MySQL root password using ALTER USER statement after install on Mac
  25. MySQL Row Format: Difference between fixed and dynamic?
  26. See if lat / long falls within a polygon using mysql
  27. mySQL error: #1248 – Every derived table must have its own alias
  28. True/False vs 0/1 in MySQL
  29. Mysql stored procedure don’t take table name as parameter
  30. Composite Primary Key performance drawback in MySQL

Leave a Comment