Drupal Performance Tip – “I’m too young to die” – know your DB engines

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 skill levels: (easiest first)

1. I’m too young to die

2. Hey, not too rough

3. Hurt me plenty

4. Ultra-violence

5. Nightmare!

  This post is rated “I’m too young too die” difficulty level.

Drupal 6 shipped with all tables being MyISAM, and then Drupal 7 changed all that and shipped with all of its tables using the InnoDB database engine. Each one with its own strengths and weaknesses but it’s quite clear that InnoDB will probably perform better for your Drupal site (though it has quite a bit of fine tuning configuration to be tweaked on my.cnf).

Some modules, whether on Drupal 6, or those on Drupal 7 that simply upgraded but didn’t quite review all of their code, might ship with queries like SELECT COUNT() which if you have migrated your tables to InnoDB (or simply using Drupal 7) then this will hinder on database performance. That’s mainly because InnoDB and MyISAM work differently, and where-as this proved as quite a fast responding query being executed on a MyISAM database which uses the main index to store this information, for InnoDB the situation is different and will result in doing a full table scan for the count. Obviously, on an InnoDB configuration running such queries on large tables will result in very poor performance

Note to ponder upon – what about the Views module which uses similar type of COUNT() queries to create the pagination for its views?