EXPLAIN SELECT p.ID, p.post_title, pm.meta_value FROM wp_posts p JOIN wp_postmeta pm ON pm.post_id = p.ID WHERE pm.meta_key = 'articles_lies' AND pm.meta_value LIKE '%1842%';. Cette requête, au cœur d’un outil interne développé pour cartographier le maillage manuel entre articles d’un site à forte volumétrie de contenu, mettait cinquante-deux secondes à s’exécuter, un délai incompatible avec un usage interactif dans un tableau de bord de rédaction.
Diagnostic : lire la sortie d’EXPLAIN ligne par ligne
La commande EXPLAIN, exécutée devant la requête problématique, renvoyait un plan d’exécution révélateur :
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | pm | ref | meta_key | ... | ... | ... | 187654 | Using where |
| 1 | SIMPLE | p | eq_ref | PRIMARY | ... | ... | ... | 1 | |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+Le nombre de lignes examinées pour la table wp_postmeta, cent quatre-vingt-sept mille six cent cinquante-quatre, correspondait à la quasi-totalité des entrées associées à la clé articles_lies sur ce site. L’index existant sur meta_key permettait bien de filtrer les lignes par clé, mais la condition LIKE '%1842%' sur meta_value, elle, ne pouvait s’appuyer sur aucun index : cette colonne est déclarée en longtext dans le schéma natif de WordPress, et MySQL ne peut indexer efficacement un filtrage par sous-chaîne positionnée n’importe où dans le texte.
Pourquoi meta_value résiste à l’indexation
La table wp_postmeta est conçue pour stocker des métadonnées de nature très variable : un identifiant numérique simple aussi bien qu’un tableau PHP sérialisé de plusieurs kilo-octets. Ce choix de conception, cohérent avec la flexibilité voulue par le cœur de WordPress, rend impossible un index classique sur l’intégralité de la colonne meta_value. Un index de type FULLTEXT existe en théorie, mais il change la sémantique de la recherche (recherche de mots entiers, pas de sous-chaîne arbitraire) et ne convenait pas à ce cas d’usage, où l’identifiant recherché pouvait apparaître n’importe où dans une liste sérialisée d’identifiants liés.
Correctif : changer la structure de stockage plutôt que la requête
Plutôt que de tenter d’optimiser la requête telle quelle, la solution retenue a consisté à changer la façon dont les liens entre articles sont stockés : au lieu d’un tableau sérialisé unique dans une seule ligne de métadonnée, chaque relation devient sa propre ligne dans wp_postmeta, avec la même clé répétée autant de fois que nécessaire :
// Avant : une seule ligne, valeur sérialisée
update_post_meta( $post_id, 'articles_lies', array( 1842, 2093, 3011 ) );
// Après : plusieurs lignes, une valeur par relation
foreach ( $articles_lies as $id_lie ) {
add_post_meta( $post_id, 'articles_lie_id', $id_lie );
}Cette restructuration permet une recherche exacte sur meta_value, une opération que MySQL sait indexer efficacement même sur une colonne longtext, tant que la comparaison porte sur une égalité stricte plutôt que sur une sous-chaîne :
ALTER TABLE wp_postmeta ADD INDEX idx_key_value (meta_key, meta_value(20));
SELECT post_id FROM wp_postmeta
WHERE meta_key = 'articles_lie_id' AND meta_value = '1842';Résultat mesuré
Après cette réécriture, la même recherche s’exécutait en moins d’une seconde, l’index composite sur meta_key et un préfixe de meta_value suffisant à cibler directement les lignes concernées sans balayage de la table entière.
Prévention pour les futurs développements
- Éviter de stocker des listes d’identifiants sérialisées quand une recherche future sur un élément de la liste est prévisible.
- Préférer une ligne de métadonnée par relation plutôt qu’un tableau sérialisé unique, dès que le volume de données dépasse quelques centaines d’entrées.
- Toujours passer par
EXPLAINavant de considérer une requête lente comme un problème de code applicatif plutôt que de structure de données.
Une requête lente n’est pas toujours un problème de requête. Ici, c’était un problème de structure de stockage choisie des mois plus tôt, sans anticiper ce cas d’usage de recherche.
En résumé
Ce diagnostic rappelle une limite structurelle de wp_postmeta souvent découverte trop tard : une colonne meta_value flexible mais difficilement indexable dès que la recherche porte sur une sous-partie de son contenu. Restructurer le stockage en une ligne par relation, plutôt que de chercher à forcer un index sur une valeur sérialisée, reste la solution la plus durable dès qu’un outil interne doit interroger ce type de donnée à des fins de performance, comme c’est le cas pour tout script d’analyse de maillage interne à grande échelle.
