wp_postmeta géante et meta_query lentes : diagnostiquer et corriger

Le symptôme était simple à décrire et difficile à vivre : la page de filtre du catalogue mettait entre huit et quatorze secondes à s’afficher, contre moins d’une seconde promise par le cahier des charges. Le site comptait 80 000 produits WooCommerce, chacun avec une trentaine de métadonnées (fournisseur, stock par entrepôt, dimensions, certifications). La table wp_postmeta avait grossi jusqu’à 6,4 millions de lignes.

Ce cas illustre un problème classique sur les catalogues volumineux : la structure clé-valeur de wp_postmeta, très pratique au départ, devient un goulet d’étranglement dès que les filtres combinent plusieurs métadonnées sur un grand nombre d’articles. Voici la démarche de diagnostic suivie, puis les corrections apportées.

Symptôme : des filtres qui s’effondrent sous la charge

Le filtre en cause combinait trois critères : la certification (« bio », « équitable »), la fourchette de prix, et la disponibilité en stock dans au moins un entrepôt. Chaque critère supplémentaire coché faisait grimper le temps de réponse de façon presque exponentielle plutôt que linéaire, ce qui est le signe typique d’un plan de requête qui dérape.

Le code utilisait WP_Query avec un meta_query à trois clauses imbriquées :

$query = new WP_Query( array(
    'post_type'  => 'product',
    'meta_query' => array(
        'relation' => 'AND',
        array( 'key' => 'certification', 'value' => $cert, 'compare' => '=' ),
        array( 'key' => 'prix_ht', 'value' => array( $min, $max ), 'type' => 'NUMERIC', 'compare' => 'BETWEEN' ),
        array( 'key' => 'stock_entrepot_1', 'value' => 0, 'compare' => '>' ),
    ),
) );

Diagnostic : lire le plan d’exécution avec EXPLAIN

La première étape a été d’activer SAVEQUERIES le temps du diagnostic pour capturer la requête SQL réellement générée par WooCommerce à partir de ce meta_query, puis de la rejouer avec EXPLAIN directement dans la base.

EXPLAIN SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_postmeta mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta mt3 ON (wp_posts.ID = mt3.post_id)
WHERE mt1.meta_key = 'certification' AND mt1.meta_value = 'bio'
  AND mt2.meta_key = 'prix_ht' AND mt2.meta_value BETWEEN 10 AND 50
  AND mt3.meta_key = 'stock_entrepot_1' AND mt3.meta_value > 0;

Le résultat était sans appel : la colonne rows affichait plusieurs centaines de milliers de lignes examinées pour chaque jointure, et la colonne key restait vide sur deux des trois jointures. MySQL ne trouvait pas d’index composite adapté à la combinaison meta_key + meta_value, et se rabattait sur l’index existant sur meta_key seul, insuffisant dès que la valeur devait aussi être filtrée.

Pourquoi les jointures multiples sur postmeta coûtent cher

Chaque clause supplémentaire d’un meta_query ajoute une jointure sur la même table wp_postmeta. Sur une petite table, MySQL absorbe facilement trois ou quatre jointures. Sur une table de plusieurs millions de lignes, chaque jointure multiplie le nombre de combinaisons à examiner, d’autant plus que la colonne meta_value est de type longtext et ne peut recevoir qu’un index préfixé, peu sélectif sur des valeurs numériques.

Correctif appliqué

Deux actions complémentaires ont réglé le problème :

• Ajout d’un index composite dédié sur (meta_key, meta_value(20)) via une migration SQL directe, au-delà de ce que l’interface d’administration permet, pour couvrir les clés les plus filtrées.
• Sortie du stock par entrepôt de wp_postmeta vers une table dédiée wp_stock_entrepots avec des colonnes typées (entrepot_id INT, quantite INT), indexée nativement sans les limites du préfixage de longtext.

Cette seconde table est alimentée par un hook sur la mise à jour du stock et interrogée directement en SQL préparé plutôt que via meta_query, ce qui a fait passer le filtre combiné de plus de dix secondes à environ 300 millisecondes.

Sur un catalogue de cette taille, la règle qu’on applique désormais systématiquement : toute métadonnée interrogée en filtre sur plus de 10 000 lignes mérite sa propre table, pas une ligne de plus dans postmeta.

Prévenir la récidive

Pour éviter que le problème ne revienne avec la prochaine métadonnée ajoutée, l’équipe a mis en place un contrôle simple : toute nouvelle clé destinée à être filtrée en masse passe d’abord par une revue rapide avec EXPLAIN en environnement de recadrage, avant d’être déployée en production. Un tableau de bord Query Monitor sur l’environnement de préproduction alerte également si une requête dépasse 200 millisecondes sur une page de catalogue.

Notre verdict

wp_postmeta reste un excellent choix pour des métadonnées peu nombreuses et peu filtrées. Dès qu’un champ devient un critère de filtre fréquent sur un catalogue de plusieurs dizaines de milliers d’entrées, la modélisation en table dédiée, avec des types de colonnes adaptés et des index composites pensés pour les requêtes réelles, reste la solution la plus robuste à long terme.