I18n : optimiser la table des traductions

La solution de gestion du multilingue de CakePHP suppose le stockage de tous les champs textuels dans une seule table nommée ‘i18n’. Le principe est simple à mettre en place et fonctionne parfaitement, mais sur une application gourmande, il convient d’optimiser cette table.

Le problème

Le schéma de la table proposé par les développeurs de CakePHP construit un index sur chacune des 4 colonnes suivantes de la table i18n :

• locale : nom de la langue
• model : le Modèle contenant le champ à traduire
• foreign_key : valeur de la clé primaire de l’enregistrement dans le Modèle
• field : nom du champ à renvoyer traduit au Modèle

Si en général la création des index sur les colonnes d’une table permet d’accélérer les requêtes, les choix proposés ne sont pas pertinents. En effet, si l’on regarde les requêtes effectuées par le comportement Translate pour renvoyer les champs traduits dans la langue demandée, on s’aperçoit que toutes font une restriction sur ces 4 colonnes à chaque fois.

Par exemple voici une requête construite par un simple find renvoyant une liste de catégories :

SELECT `Category`.*, `I18n__name`.`content`, `I18n__slug`.`content` FROM `categories` AS `Category` LEFT JOIN `i18n` AS `I18n__name` ON (`Category`.`id` = `I18n__name`.`foreign_key` AND `I18n__name`.`model` = 'Category' AND `I18n__name`.`field` = 'name') LEFT JOIN `i18n` AS `I18n__slug` ON (`Category`.`id` = `I18n__slug`.`foreign_key` AND `I18n__slug`.`model` = 'Category' AND `I18n__slug`.`field` = 'slug') WHERE 1 = 1 AND `Category`.`parent_id` IS NULL AND `I18n__name`.`locale` = 'fre' AND `I18n__slug`.`locale` = 'fre' ORDER BY `Category`.`lft` ASC

Le plugin DebugKit nous indique que cette requête prend 3ms en environnement de développement (une éternité !). Lançons la requête à nouveau avec un EXPLAIN :

MySQL nous confirme qu’il pourrait utiliser les clés locale, model, foreign_key et field, mais qu’il n’utilise que foreign_key…

Solution

Essayons maintenant en supprimant ces 4 index et en créant un seul index composé des 4 colonnes :

[Attention : le colorateur syntaxique met le nom de champ 'field' en majuscules ci-dessous, il faut penser à le remettre en minuscule.]

ALTER TABLE `i18n` DROP INDEX `locale`;
ALTER TABLE `i18n` DROP INDEX `model`;
ALTER TABLE `i18n` DROP INDEX `foreign_key`;
ALTER TABLE `i18n` DROP INDEX `field`;
 
ALTER TABLE `i18n` ADD INDEX `i18n_index` (`locale`,`model`,`foreign_key,`FIELD`);

MySQL renvoie une erreur : « #1071 – Specified key was too long; max key length is 1000 bytes ». L’index que nous souhaitons créer est trop long. En effet, les champs ‘model’ et ‘field’ sont de type VARCHAR de longueur 255, soit la longueur maximum. Nous n’avons pas besoin d’un tel nombre de caractères pour stocker des noms de Modèles ou de champs ! Nous choisissons de les réduire à 20 caractères :

ALTER TABLE  `i18n` CHANGE  `model`  `model` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE  `i18n` CHANGE  `field`  `field` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

Ceci fait nous tentons à nouveau de créer notre index composé :

ALTER TABLE `i18n` ADD INDEX `i18n_index` (`locale`,`model`,`foreign_key,`FIELD`);

Cette fois l’index est bien créé.

Résultat

Nous rechargeons la page et cette fois, DebugKit nous annonce que la requête ne prend plus qu’une milliseconde, soit 3 fois moins de temps. Un EXPLAIN nous montre cette fois que MySQL utilise bien notre nouvel index :

Ce gain, multiplié par un nombre élevé de requêtes qui appellent la table i18n, peut considérablement accélérer une application multilingue.

Pierre-Emmanuel Fringant