Drupal Performance Tip – “I’m too young to die” – indexes and SQLs

In the spirit of the computer video game Doom and its skill levels, we’ll review a few ways you can improve your Drupal speed performance and optimize for better results and server response time. These tips that we’ll cover may be at times specific to Drupal 6 versions, although you can always learn the best practices from these examples and apply them on your own code base.

Doom

Using indexes, and proper SQL queries can boost performance by a huge factor, especially if the affected tables are very big (millions of rows). Take a look at the diff below showing a fix to a not so proper, and ill-advised use of querying the database:

The bad performing query took anything between 6 to 60 seconds to run, depending on the data, and database load, and database’s current cache state. The newer query takes milliseconds.