May 2012

MySQL datatype for storing IP addresses


The best way to store a (version 4) IP address in a MySQL database is as an integer. This may sound strange but MySQL has two powerful functions to enable you to store an IP address as an unsigned INT. Searching an integer is much faster than searching a string and additionally integers take up less storage space which is great when working with large datasets.

mysql> SELECT INET_ATON('10.0.5.9');
---------------------
INET_ATON('10.0.5.9')
---------------------
167773449
---------------------
1 row in set (0.00 sec)

and the reverse function

mysql> SELECT INET_NTOA(167773449);
---------------------
INET_NTOA(167773449)
---------------------
10.0.5.9
---------------------
1 row in set (0.00 sec)

MySQL datatype for storing IP addresses Read More »

Speeding up Firefox on OSX

Firefox had been running really slowly lately and as I am an awful hoarder of tabs closing the application took forever. I’d heard that since version 3 there was a sql lite table that according to Google could do with vacuuming – whatever that meant.

The SQLite database uses flat files for local storage and I wondered how big and bloated they had become over time. It turned out to be much worse than I expected. To view I typed the following into a command terminal
find ~/Library/Application\ Support/Firefox/Profiles -type f -name '*.sqlite' -exec ls -arlth {} \;
My .places file was 40 megabytes which certainly wasn’t helping speed at all. The following command took care of the necessary housekeeping
find ~/Library/Application\ Support/Firefox/Profiles -type f -name '*.sqlite' -exec sqlite3 {} VACUUM \;
find ~/Library/Application\ Support/Firefox/Profiles -type f -name '*.sqlite' -exec sqlite3 {} REINDEX \;

Speeding up Firefox on OSX Read More »