Une jointure entre trois tables personnalisées ralentissait un back-office

EXPLAIN renvoyait, sans surprise mais sans consolation non plus, trois lignes marquées type: ALL : un balayage complet de table sur chacune des trois tables impliquées dans la requête. Le tableau de bord interne d’un réseau de commerces de proximité, qui agrégeait les ventes, les stocks et les commandes fournisseurs sur une même vue, mettait près de sept secondes à s’afficher, un délai devenu inacceptable depuis que le nombre de points de vente affiliés avait triplé en un an.

Ce tableau de bord ne s’appuyait pas sur les tables natives de WordPress, mais sur trois tables personnalisées créées lors du développement initial de l’extension métier : wpm_ventes, wpm_stocks et wpm_commandes_fournisseurs, chacune liée aux deux autres par un identifiant de point de vente et une référence produit.

La requête en cause

La requête agrégeait les trois tables pour produire, pour chaque produit et chaque point de vente, le volume de ventes, le stock restant et les commandes en cours :

SELECT v.produit_id, v.point_vente_id,
       SUM(v.quantite) AS total_ventes,
       s.quantite_stock,
       c.quantite_commandee
FROM wpm_ventes v
JOIN wpm_stocks s
     ON s.produit_id = v.produit_id
    AND s.point_vente_id = v.point_vente_id
JOIN wpm_commandes_fournisseurs c
     ON c.produit_id = v.produit_id
    AND c.point_vente_id = v.point_vente_id
WHERE v.date_vente >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY v.produit_id, v.point_vente_id;

Aucune des trois tables ne disposait d’un index sur les colonnes produit_id et point_vente_id, utilisées pourtant comme condition de jointure. MySQL devait donc, pour chaque ligne de wpm_ventes, parcourir intégralement wpm_stocks puis intégralement wpm_commandes_fournisseurs à la recherche d’une correspondance.

Pourquoi le problème n’était apparu que récemment

Avec une quinzaine de points de vente et quelques centaines de références produit, chaque table restait suffisamment petite pour que ce balayage complet passe presque inaperçu, de l’ordre de quelques centaines de millisecondes. Le triplement du réseau de points de vente en un an avait fait grimper la taille de chaque table dans des proportions bien supérieures, l’effet d’un balayage complet croissant de façon multiplicative avec le nombre de lignes de chacune des trois tables jointes, et non de façon linéaire.

Le diagnostic avec EXPLAIN

La commande EXPLAIN, préfixée devant la requête, a confirmé l’absence totale d’utilisation d’index :

EXPLAIN SELECT v.produit_id, v.point_vente_id, ...
-- id | table | type | possible_keys | key  | rows
-- 1  | v     | ALL  | NULL          | NULL | 48210
-- 1  | s     | ALL  | NULL          | NULL | 3190
-- 1  | c     | ALL  | NULL          | NULL | 2740

Chaque ligne de wpm_ventes déclenchait un parcours complet des deux autres tables, soit un ordre de grandeur de plusieurs centaines de millions d’opérations de comparaison pour l’ensemble de la requête.

Le correctif : un index composite sur chaque table

Un index composite, couvrant les deux colonnes utilisées conjointement dans les conditions de jointure, a été ajouté sur chacune des deux tables jointes :

ALTER TABLE wpm_stocks
    ADD INDEX idx_produit_point_vente (produit_id, point_vente_id);

ALTER TABLE wpm_commandes_fournisseurs
    ADD INDEX idx_produit_point_vente (produit_id, point_vente_id);

Un index a également été ajouté sur wpm_ventes, cette fois sur la colonne date_vente utilisée dans la clause WHERE, pour éviter un balayage complet sur cette table également lors du filtrage des trente derniers jours :

ALTER TABLE wpm_ventes
    ADD INDEX idx_date_vente (date_vente);

Le temps de chargement du tableau de bord est passé de 6,8 secondes à 0,4 seconde après ces trois ajouts, sans qu’aucune ligne de la requête elle-même n’ait été modifiée.

Prévention pour les futures tables personnalisées

• Toute colonne utilisée dans une condition de jointure ou de filtrage mérite un index dès la création de la table, même si le volume de données initial semble négligeable.
• Une revue systématique des tables personnalisées créées par les extensions maison a été ajoutée à la procédure de recette technique, avant chaque mise en production d’une nouvelle fonctionnalité.
• La commande EXPLAIN a été intégrée aux tests automatisés critiques, avec une alerte si une requête clé révèle un type: ALL sur une table dépassant un certain volume.

Une table personnalisée sans index se comporte bien tant qu’elle reste petite ; la croissance du volume de données révèle le problème d’un coup, souvent bien après que la structure a été figée.

En résumé

Trois tables maison, jointes sans le moindre index sur leurs clés de correspondance, ont fini par faire s’écrouler les performances d’un tableau de bord interne à mesure que le réseau de points de vente grandissait. L’ajout de trois index composites, sans toucher à la logique métier de la requête, a suffi à diviser le temps de chargement par plus de dix-sept.