Speeding up international Drupal versions

Submitted by Frederic Marand on
Drupal tips and tricks

Would you like your non-english Drupal site to be more responsive ? I just found two-minute hack to speed it up, by a factor of four on the new Riff hosting. Here's how.

While revising devel.module in order to update the french translation, I noticed the queries performed by locale() took on the order of 20ms to be performed, and there are a whole lot of them on most pages when locale.module is enabled.

Since this seemed abnormally slow, I looked at the structure, and saw why. The current MySQL code goes:

--
-- Table structure for table 'locales_source'
--

CREATE TABLE locales_source (
  lid int(11) NOT NULL auto_increment,
  location varchar(255) NOT NULL default '',
  source blob NOT NULL,
  PRIMARY KEY (lid)
);

But MySQL allows blob indexing ! So let's make just a small change:

--
-- Structure de la table `locales_source`
--

CREATE TABLE locales_source (
  lid int(11) NOT NULL auto_increment,
  location varchar(255) NOT NULL default '',
  source blob NOT NULL,
  PRIMARY KEY  (lid),
  KEY `source` (`source`(40))
);

That's it: just add an index over the first 40 bytes of the blob column. The cost will be low because this table is not updated in production situations, so index maintenance costs nothing.

The benefits are immediate and enormous:

  • while the locale calls used to take around 20ms, they're down to around 0.5ms, which more or less means a 97,5% time reduction for locale() (40x speedup)
  • the overall page build has also been reduced dramatically, from about 500ms for a page like admin/blocks down to 125ms, which still means a 4x speedup overall

Chatting on #drupal soon proved this was already a known issue/trick with drupal 4.7, but not yet formally documented or included in the code for portability reasons. This is discussed in issue #42463.

Keeping in mind the issues raised in that discussion, it's still a great little fix to have in one's toolbox.