High Performance Drupal

Chapter 15. MySQL Query Optimization

We noted in a previous chapter the importance of MySQL query optimization in relation to MySQL configuration tuning. 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. As in the other MySQL chapters, we need to note that this subject is massive and we cannot possibly cover it fully here. It is highly recommended that you get a book on this subject, as it is a very deep one and is well worth learning for any Drupal developer. As a web developer using a CMS, you are only slightly removed from the SQL layer. Not deeply knowing how to use this layer and how to optimize it is very limiting. To get you started, this chapter will cover some very basic optimization, index usage, and join optimization techniques.

Index Basics

Indexes are very important for database performance. But even though they have this level of importance, they are not completely understood by many developers. This often leads to problems that could easily have been avoided. The biggest issue is a belief that indexes are kind of magical and that the MySQL optimizer should be able to run a query quickly if an index even touches the columns in question. Sadly, indexes are not magic.

It is best to think of an index as a tree, largely because they are trees in most DB systems (B+Trees, specifically; there is a link to an excellent article on B+Trees and how InnoDB uses them at the end of ...