Views Index Hint module

On one of my projects, I cam across a certain performance edge case on a site with a large volume of data. For whatever reason, the MySQL optimizer refused to use the fastest index in a handful of views queries, causing these queries to be painfully slow.

Here is a simplified example of a query generated by views:

SELECT nid, title FROM node WHERE status <> 0 AND type IN ("story") ORDER BY created DESC;

When viewing the explain plan for this query, it shows my that MySQL is choosing to use the node_type index instead of node_created.

Website Performance Slides

I gave a presentation tonight to the Dayton Web Developers meetup on Website Performance. The presentation gave a high level view of both back end and front end performance considerations.

Drupal Performance Tuning

I work on several high traffic Drupal sites that get millions of page views per month. These sites require special fine tuning to make sure they stay online under heavy load and serve pages to users quickly.

Tuning a Drupal site for performance involves looking at several layers of the web application.

  1. The Web Server
  2. The PHP code
  3. The Database and queries
  4. HTML Components
  5. Javascript

Items 1 to 3 are in the category of Back End Performance and involve delivering the main HTML document to the user's browser as quickly as possible.

Drupal performance and the variable cache

Lately, several of the Drupal sites I work on with large amounts of traffic have been showing many sluggish queries. One query that kept constantly showing up was this one:

SELECT * FROM variable;

Drupal stores most of it's settings in the variable table, but to improve query performance it stores all the variables from this table into a single record inside the cache table. However, when a variable is changed, added or deleted that cache must be updated.

The reason the above query was getting executed so frequently was because of a variable_set() call inside the Views module that was run on every page load.

I've seen this mistake more than once, so keep in mind that you should only add, change or delete variables during administrative tasks.