Indexer wp_posts sur post_modified pour accélérer un sitemap trié par fraîcheur

Trois cent quarante millisecondes contre quatre secondes et cent millisecondes : c’est l’écart mesuré, sur la même requête, avant et après l’ajout d’un index composite sur la table wp_posts d’un catalogue de fiches produit dépassant les deux cent mille lignes. Le sitemap personnalisé de ce site trie ses entrées par date de modification décroissante, pour que les moteurs de recherche priorisent les contenus les plus récemment mis à jour. Une logique saine, mais coûteuse sans le bon index.

Le problème : un tri qui balaie toute la table

La requête à l’origine de la lenteur ressemblait à ceci :

SELECT ID, post_modified
FROM wp_posts
WHERE post_type = 'product'
  AND post_status = 'publish'
ORDER BY post_modified DESC
LIMIT 500 OFFSET 4500;

Sur une table de cette taille, sans index adapté, MySQL doit d’abord filtrer les lignes correspondant au type et au statut, puis trier l’ensemble du résultat intermédiaire par date avant d’appliquer la limite. La commande EXPLAIN confirmait le diagnostic :

EXPLAIN SELECT ID, post_modified FROM wp_posts
WHERE post_type = 'product' AND post_status = 'publish'
ORDER BY post_modified DESC LIMIT 500 OFFSET 4500;

-- type: ALL
-- rows: 198432
-- Extra: Using where; Using filesort

La mention Using filesort dans la colonne Extra signale que MySQL doit trier les résultats en mémoire ou sur disque après les avoir récupérés, faute d’index utilisable pour l’ordre demandé. Sur deux cent mille lignes, ce tri devenait le principal poste de coût, répété à chaque publication ou mise à jour de fiche produit puisque le script régénérait entièrement le sitemap concerné.

La solution : un index composite ciblé

WordPress installe par défaut un index sur post_type et post_status combinés à post_date, mais aucun sur post_modified, une colonne pourtant très sollicitée par les sitemaps et les flux RSS triés par fraîcheur. La création d’un index dédié règle le problème :

ALTER TABLE wp_posts
ADD INDEX idx_type_status_modified (post_type, post_status, post_modified);

L’ordre des colonnes dans l’index composite n’est pas arbitraire : MySQL utilise un index multi-colonnes de gauche à droite, donc placer post_type et post_status en premier permet de filtrer efficacement avant de profiter du tri déjà ordonné par post_modified pour la portion restante. Après création de l’index, le même EXPLAIN affichait :

-- type: ref
-- rows: 4812
-- Extra: Using index condition

La mention Using filesort a disparu, et le nombre de lignes examinées est passé de la totalité de la table à un sous-ensemble correspondant réellement aux critères, un facteur supérieur à quarante en réduction du volume de données parcouru.

Un coût qu’il ne faut pas ignorer

Ajouter un index n’est jamais gratuit : chaque écriture sur la table (publication, modification, suppression) doit désormais mettre à jour cette structure supplémentaire. Sur un catalogue avec des mises à jour très fréquentes en masse, par exemple via un import de flux fournisseur toutes les heures, ce coût d’écriture mérite d’être mesuré avant généralisation, avec un test de charge représentatif du volume réel de modifications quotidiennes.

Vérifier l’impact réel avec WP-CLI

Pour mesurer le temps de génération complet du sitemap avant et après la modification, un chronométrage simple en ligne de commande suffit :

wp eval 'file_put_contents( "/tmp/sitemap-test.xml", generer_sitemap_produits() );' --skip-plugins

Encadrée par la commande time du terminal, cette exécution isolée du reste de la pile applicative donne une mesure fiable, non polluée par un cache de page qui masquerait le gain réel sur les visites suivantes.

• Identifier la requête responsable de la lenteur avec le journal des requêtes lentes de MySQL, activé temporairement en environnement de test.
• Confirmer le diagnostic avec EXPLAIN avant toute modification de schéma.
• Créer l’index composite en heures creuses sur une table volumineuse, l’opération verrouillant potentiellement l’écriture selon le moteur de stockage utilisé.
• Mesurer à nouveau avec EXPLAIN, puis avec un chronométrage réel du script complet.

Un index qui n’existe pas ne coûte rien tant que personne ne trie sur la colonne concernée. Le jour où un script de sitemap, un flux RSS ou un tableau de bord commence à trier par date de modification sur une grosse table, cette absence devient soudain très visible.

En résumé

Ce cas illustre une règle simple mais souvent oubliée : la structure d’index par défaut de WordPress est pensée pour les usages natifs du cœur, pas pour les scripts personnalisés qui trient différemment les données. Avant d’optimiser le code d’un générateur de sitemap, il vaut la peine de vérifier, avec EXPLAIN, si le ralentissement ne vient pas simplement d’un index manquant, une correction qui prend quelques minutes et qui évite de complexifier inutilement la logique applicative.