Performance - A Guide For Best Practice

Comparing #15 with #16

Revision #16 was created by Y!! Y!! on Feb 5, 2010, 8:33:33 PM.

Fixed typo

Content

[...]
## Working With IP Addresses ##

Most PHP applications I've seen save ip addresses as a `string` of column type `VARCHAR(15)`. This works well and is already fast when used with an index. Still there is a much better solution.

PHP has the function [`ip2long()`](http://php.net/manual/en/function.ip2long.php) which converts an ip address into a numerical value. The ip address `127.0.0.1` of type `string` would be converted into `2130706433` of type `integer`. The function to do the conversion vice-versa is called [`long2ip()`](http://php.net/manual/en/function.ip2long2ip.php). In MySQL these two functions are called [`INET_ATON()`](http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton) and [`INET_NTOA()`](http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-ntoa).

The type of the column for such a numerical ip address must be `INT(10) UNSIGNED`.

So when saving ip addresses as numerical values, you have the benefit of faster selections/sorting and the size of the table will be smaller since `INT(10)` consumes 4 bytes whereas `VARCHAR(15)` consumes at least 16 bytes or more (depends on the used charset).
[...]