Mysql insider

To write efficient Mysql-powered applications a little insight in how Mysql works is needed. One often overlooked example of this is Mysqls silent column changes features, which may cause your tables to look different than you specified. I’m sure most people know, that using chars is generally faster than varchars, but did you know that Mysql may actually change your char-columns to varchars? This happens if you have a variable length column in a table (ie. one varchar and two chars in a table causes all to be converted varchars). You can find more on the automatic column conversions in the Mysql manual.

There are several ways to keep performance up and avoiding the silent column changes done by Mysql. One obvious is just use char-columns consequently even though most of the space used by the column is empty space. Another efficient solution is to loose the book on database normalization and isolate the variable length columns (such as a text-field or blob for binary data) either in a “varchars table” or even in separate table with the primary key from their original table as the only other key. In each case you should join the data into searches – when you need then (and forget about their existence when you don’t. It may add a little complexity to the application, but as your tables grow and grow, the performance doesn’t dive as much as it would otherwise.

Another common misconception with databases is that adding indexes is only a good thing as it increases the speed on searches. This may be the case often, but an index comes with a price – the updates and inserts gets slower for each index – and some one has even taken the time to prove just how much indexes can hurt. Remember this and only add the indexes you need (and learn this form the use of the application, not the theorists as they may know the optimum best, but not the real world most efficient).

Did you know Mysql will only use one index per table when processing a query? If not you should probably also take a peak at Mysql Performance Tuning (especially page 3) on the Linux Magazine website.