Getting rid of slow queries

If you run a popular Drupal site, you'll sometimes find more queries in your MySQL server's slow query log than you really want to.

Sometimes the unruly queries can be eliminated easily by adding an index or two. Sometimes, they can be avoided alltogether (for example, the query in theme_forum_topic_navigation can be avoided by overriding the function with an empty one in your theme).

Sometimes, you need to be more ingenious. The queries in tracker.module have plagued drupal.org for a log time now. They added about 2000 slow query log entries per week on the slave server and did contribute to the melt-down we experienced last summer. There have been a lot of suggestions on how to best deal with them, but none was really a good solution.

The problem could only be resolved by a rewrite of the tracker module to use index tables. This rewrite is called tracker2 and was created by David Strauss. Since I deployed the module on drupal.org about two weeks ago, the tracker induced slow queries have disappeared alltogether. Great work, David!

Now, we need to find a way to deal with the slow queries generated by the rest of the modules, such as the search module. If you are interested in helping out: the slow query logs get posted to the infrastructure list each Saturday.