Feature

A large part of MySQL optimization lies in improving poorly performing SQL queries. While tuning is important, it often has nowhere near the impact of actually fixing a poorly performing query. Fixing queries is also a lot more fun. Obviously query optimization is a large subject, and can’t possibly be covered in full in a single article. I highly recommend that you get a book on this subject; for any Drupal developer, it is well worth learning.

As a web developer using a CMS, you are only slightly removed from the SQL layer. Not completely knowing how to use this layer and how to optimize it is very limiting. To get you started, we will cover some very basic optimization, index usage, and join optimization techniques.

Index Basics

Even though indexes are very important for database performance, they are not completely understood by many developers, which often leads to easily-avoidable problems. The main issue is the mystical belief that the MySQL optimizer should be able to quickly run a query if an index so much as touches the columns in question. Sadly, indexes are not magical.

It is best to think of an index as a tree, largely because they are trees in most DB systems. (B+Trees, specifically; for more information, see http://wdog.it/4/1/btree.)

Thus, if you have an example index test that covers (columnA, columnB), you literally have a tree of columnA values, with columnB values in the leaves. If you have a query that has a WHERE condition on these two columns, MySQL will go through this tree looking for the correct columnA value first, and then go into the leaves of that object, and find the correct columnB value.