Deux colonnes, un seul ordre correct. C’est le résumé de ce qui ralentissait une recherche de fiches produits par caractéristique technique sur un catalogue de plusieurs milliers de références, alors qu’un index composite existait bel et bien sur wp_postmeta.
La requête filtrait sur meta_key = 'reference_fabricant' puis sur une valeur précise de meta_value, un schéma extrêmement courant dès qu’on interroge des métadonnées via WP_Query avec un meta_query. L’index composite avait été créé dans l’ordre (meta_value, meta_key), hérité d’un exemple trouvé en ligne sans vérification, plutôt que dans l’ordre attendu par ce type de filtre.
Symptôme : un index présent mais un temps de réponse élevé
La requête mettait plus de deux secondes à s’exécuter sur une table de métadonnées comptant plusieurs millions de lignes, alors que la même structure de requête, testée sur une table de test plus petite, répondait en quelques millisecondes. La différence n’apparaissait qu’à volume réel, ce qui avait longtemps masqué le problème pendant la phase de développement.
Diagnostic : un index inutilisé pour la bonne raison
Un EXPLAIN sur la requête montrait que l’index composite n’était pas utilisé du tout pour la partie meta_key, MySQL préférant un balayage plus large. La raison tient au fonctionnement d’un index composite B-tree : il n’est pleinement exploitable, pour une recherche d’égalité, que si les colonnes filtrées correspondent au préfixe gauche de l’index, dans l’ordre où elles ont été déclarées.
-- Index existant, ordre inversé
ALTER TABLE wp_postmeta ADD INDEX idx_meta_valeur_cle (meta_value(191), meta_key);
-- Requête typique générée par WP_Query
SELECT post_id FROM wp_postmeta
WHERE meta_key = 'reference_fabricant'
AND meta_value = 'REF-4471-B';
Avec meta_value en première position, MySQL ne peut pas se servir de l’index pour filtrer efficacement sur meta_key seul, puisque ce n’est pas la colonne de tête. Le moteur retombait sur un parcours bien plus coûteux du reste de la table.
Correctif : inverser l’ordre des colonnes
ALTER TABLE wp_postmeta DROP INDEX idx_meta_valeur_cle;
ALTER TABLE wp_postmeta ADD INDEX idx_meta_cle_valeur (meta_key, meta_value(191));
Avec meta_key en tête, l’index devient directement exploitable pour isoler rapidement le sous-ensemble de lignes correspondant à la clé recherchée, avant même de comparer la valeur. Le temps de réponse est descendu à moins de 40 millisecondes sur le même volume de données après ce changement.
Pourquoi la longueur de préfixe compte aussi
Le (191) appliqué à meta_value limite l’index aux 191 premiers caractères de la colonne, une contrainte liée à la taille maximale d’un index InnoDB avec l’encodage utf8mb4. Omettre cette longueur sur une colonne de type texte long empêche parfois purement et simplement la création de l’index.
Prévention : ANALYZE TABLE après un changement de volume important
Après toute modification structurelle de ce type, exécuter ANALYZE TABLE wp_postmeta; permet à MySQL de recalculer ses statistiques internes sur la répartition des valeurs, évitant que l’optimiseur continue de raisonner sur une image ancienne de la table lors du choix du prochain plan d’exécution.
Vérifier l’ordre d’un index existant sans le recréer
Avant de se lancer dans une reconstruction d’index, souvent longue sur une grande table, il est possible d’inspecter l’ordre exact des colonnes d’un index existant directement depuis le schéma :
SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'wp_postmeta'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
La colonne SEQ_IN_INDEX donne la position exacte de chaque colonne dans l’index, une valeur de 1 correspondant toujours à la colonne de tête. Sur ce projet, cette requête a permis de confirmer en quelques secondes que l’index existant plaçait bien meta_value en position 1, sans avoir à relire la définition complète de la table dans un outil d’administration graphique.
En résumé
Un index composite n’est pas une garantie de performance en soi : son ordre de colonnes doit correspondre précisément à la façon dont les requêtes filtrent réellement les données. Avant de conclure qu’un index est inutile, il vaut mieux vérifier son ordre de déclaration face à l’ordre des conditions de la requête qui doit s’en servir.
