Z-Car

Ramblings about everything from Cars to Computers

How do I store an IP Address in MySQL? INET_AtoN!

By Prodromus August 19, 2010 MySQL 1 Comment

One of the most popular questions out there is, “How do I store an IP Address in MySQL?”. The fastest and most compact way to store an IP address in the format “10.51.1.10” is as an unsigned integer. If you store the actual values as a char(15), you will require 11 extra bytes per row. In addition, using IP as a condition when querying is much faster when using an unsigned integer. Testing has shown that range queries can result in as much as a 35 million times improvement over storing an IP address as a character string. Yes, that is 35 million!

Simply define IP as an INT UNSIGNED.

Use MySQL’s built-in function INET_ATON() to convert the string representation of the IP Address into an unsigned Integer. INET_NTOA() will perform the reverse translation.

This will result in space savings, and faster queries, who could ask for more than that?

Show Processlist and Show Full Processlist – MySQL Tip

By Prodromus August 19, 2010 MySQL No Comments

Everyone knows that using the MySQL command Show Processlist will display all current connections, like the following :

What is little known however is that the Show Full Processlist command will display the full query in the Info column. By default, Show Processlist will only display the first 100 characters. One thing to keep in mind about the Info line is that while the statement (query) might be the one sent to the server, it will display the innermost statement if the statement executes other statements.

How to increase MySQL performance when loading a Dump File

By Prodromus August 19, 2010 MySQL, Restore Backup No Comments

In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.

Return of Blue Meanie – HD Video on SmugMug

By Jimmy August 18, 2010 Animals No Comments

I have been a long-time user of SmugMug Photo service, they were the best when I first started to look for an online photo gallery, and they still are. For $40 a year, you get unlimited storage AND they store all of you original photos (no reduced image quality and resolution like free sites). A great new feature is their ability to store and play-back HD quality video. My wife and I bought a Sony HD camcorder a couple years back, and it is great to now be able to load them on to the Interwebs and have them viewable from anywhere. Here is a video we made of old Blue Meanie several years ago as he went after his dinner.

To see the above content in pure HD glory, click on the full screen button on the bottom right, and then pick HD On.

Some more SmugMug highlights:

* – Integrated sharing on Facebook, Twitter, via email and a growing list of other services
* – Mobile Apps for iPad, iPhone and Android
* – No ads or promotions surrounding your photos
* – We keep original, full resolution photos, unlike other sites
* – Full-screen slideshows and HD Video playback
* – Real, knowledgeable & friendly humans to answer your emails quickly
* – Add photos from Lightroom, Aperture, iPhoto, Picasa, and other popular programs

$Blue Lobster - \$

A-10 Thunderbolt II by Fairchild

By Warbird August 13, 2010 A-10 Thunderbolt II, Fairchild Republic No Comments

Great shot of an A-10 over Afghanistan. In this picture, Capt. Andrew Quinn flies his OA/A-10 Thunderbolt II observation/attack aircraft to a refueling position behind a KC-135 Stratotanker. This picture was taken on Sunday, March 26, 2006 by U.S. Air Force photo/Master Sgt. Lance Cheung. Captain Quinn is currently deployed to the 355th Expeditionary Fighter Squadron at Bagram Air Base, Afghanistan. The A-10 Thunderbolt II is a single-seat, twin-engine, straight-wing jet aircraft designed to provide close air support (CAS) of ground forces.

It was the first U.S. Air Force aircraft, designed in the 1970’s, exclusively for close air support . The A-10’s official name comes from the Republic P-47 Thunderbolt of World War II, a fighter that was particularly effective at close air support. The A-10 is more commonly known by its nickname “Warthog” or simply “Hog”.

MySQL – Should you put an index on a Boolean field to help query performance?

By Prodromus June 14, 2010 MySQL, Query Optimization 2 Comments

I am often asked if it makes sense to place an index on a Boolean field in order to improve query performance. In general, because a boolean value can only have three values (True, False, Null), this low cardinality would suggest that adding an index will not help performance, as the query optimizer will still usually perform a table-scan if you have an even distribution of values within your DB.

One situation in which an index on a boolean field (or other low cardinality field) might be useful is if there are relatively few of one of the values, for example 5 True values in a table of millions of records and you are searching for those few values on a regular basis.

However, you might index a boolean value on a combination of fields. Indexing on a single Boolean might be pointless, because there’s only 2 (or 3) values. However, indexing on 16 boolean values has the potential of 2^16 values. It might help to make a combined index but you should understand how the combined index can and cannot be used and be aware that the order of the columns matters.

In general, you should always profile your system to see if there are queries that are too slow and consider adding another index to handle those queries. Sometimes a single combined index can be used for multiple queries and others time you will need to make an index for each type of query. Remember that adding indexes slows down modifications to the data so it is possible to have too many indexes. There is always a trade-off when creating multiple indexes.