Expertise PrestaShop

Conseils techniques e-commerce et PrestaShop

Une question ? Contactez Les Vikings

Index MySQL : optimiser WHERE, JOIN et ORDER BY avec EXPLAIN

Optimisez les index MySQL pour PrestaShop : sargabilité, index composites, EXPLAIN/ANALYZE, tri/LIMIT, JOINs et déploiement sécurisé en production.

Kévin DECQ-CAILLET

|

|

Mis à jour le Lecture : 13 min

Texte SQL et schémas de base de données sur un écran d'ordinateur.

Table des matières :

  1. Ce que mesure réellement un index InnoDB (et pourquoi PrestaShop en souffre souvent)
  2. Lire EXPLAIN sans se raconter d’histoires : champs clés, pièges, EXPLAIN ANALYZE
  3. Optimiser WHERE : prédicats sargables, ordre des colonnes, index composites
  4. Optimiser JOIN : indexer les colonnes de jointure, contrôler la cardinalité, éviter les plans absurdes
  5. Optimiser ORDER BY : éviter filesort/temporary, indexer pour le tri + LIMIT, pagination stable
  6. Déployer des index en prod sans casser : mesure, migrations, verrous, rollback

Un index MySQL utile ne “rend pas une requête rapide” par magie : il réduit le volume de lignes/feuilles à parcourir et évite du tri/du temporaire. Sur un stack e-commerce PrestaShop (8.1/8.2/9.x) typiquement sur MySQL 8.0 ou MariaDB 10.6+, l’optimisation passe presque toujours par le triptyque WHERE / JOIN / ORDER BY, et par une lecture froide de EXPLAIN (et idéalement EXPLAIN ANALYZE). Le reste (cache applicatif, Varnish, CDN) ne compensera pas des plans d’exécution catastrophiques à répétition.

Dans la vraie vie (boutiques FR/UE avec multi-boutique, multi-langue, flux marketplace, modules “stats/log”), on observe souvent le même scénario : la requête est correcte fonctionnellement, mais elle devient imprévisible dès que la volumétrie grimpe (pics de trafic, import catalogue, soldes) ou que l’infra change (migration vers un datacenter plus éloigné, passage d’un DBaaS, réplication). Les index n’éliminent pas la latence réseau, mais ils évitent que la base fasse “du travail inutile” sur chaque page.

Ce que mesure réellement un index InnoDB (et pourquoi PrestaShop en souffre souvent)

En InnoDB, un index est une structure B-Tree (ou variante) conçue pour naviguer rapidement par ordre de clé. Le point que beaucoup ratent : l’index n’est pas une “table parallèle” complète. Pour un index secondaire, InnoDB stocke la clé indexée + la clé primaire pour pouvoir “remonter” à la ligne (bookmark lookup). La doc MySQL le dit explicitement : « Secondary indexes in InnoDB store the primary key columns along with the secondary index columns ». Résultat direct : si votre clé primaire est large (ou pire, non stable), tous les index secondaires gonflent.

Deux conséquences très concrètes :

  • Plus d’espace disque (et plus de pages à maintenir).
  • Plus de pression sur le buffer pool : si vos index secondaires “mangent” la RAM, vous augmentez mécaniquement les lectures disque et donc la latence.

Un repère simple (ordre de grandeur) : une clé primaire INT (4 octets) n’a rien à voir avec une PK CHAR(36) (UUID). Sans faire de pseudo-précision, la tendance est claire :

Choix de clé primaire Taille typique Impact sur les index secondaires
INT auto-increment ~4 octets Faible
BIGINT auto-increment ~8 octets Modéré
CHAR(36) (UUID texte) ≥ 36 octets Fort (bloat + cache moins efficace)

PrestaShop n’aide pas toujours. Le schéma historique a des index “OK” pour les cas standards, mais dès qu’un module ajoute des filtres, des jointures ou des tris non prévus, on tombe sur des requêtes qui passent de “quelques ms” à “plusieurs centaines de ms” dès que la volumétrie grimpe (catalogue 100k produits, order_detail en millions de lignes, multi-boutique). Autre problème récurrent : la base n’a pas systématiquement des foreign keys déclarées (choix historique pour éviter les contraintes en upgrade). Les index existent souvent, mais pas toujours sur les colonnes réellement utilisées dans les JOIN/WHERE.

Un symptôme classique sur des boutiques avec beaucoup de données “géographiques” (livraison, taxes, zones) : on filtre fréquemment sur des colonnes comme id_country, id_zone, id_state, id_carrier, id_shop, id_lang. Si un module introduit une requête du type “récupère les règles de taxe pour ce pays + groupe + boutique” et qu’il manque un index composite cohérent, l’addition peut vite devenir visible sur le tunnel de commande (où chaque milliseconde compte).

Enfin, chaque index a un coût : écriture plus lente (INSERT/UPDATE/DELETE), plus d’IO, plus de mémoire (buffer pool), plus de temps d’ALTER TABLE. C’est exactement le genre de compromis qu’il faut documenter et monitorer en prod. Pour le volet “hygiène” (ANALYZE, nettoyage, tables qui grossissent), vous avez déjà un bon socle côté PrestaShop : Base de données PrestaShop : routine de maintenance et nettoyage automatisé.

Lire EXPLAIN sans se raconter d’histoires : champs clés, pièges, EXPLAIN ANALYZE

Sur MySQL 8, vous avez trois niveaux : EXPLAIN, EXPLAIN FORMAT=JSON, et EXPLAIN ANALYZE. Le premier donne un résumé tabulaire (utile pour trier vite les problèmes), le JSON expose des détails (coûts, “usingtemporarytable”, “filesort”, condition filtering), et EXPLAIN ANALYZE exécute réellement la requête en instrumentant le temps passé par opérateur.

Les colonnes/tab clés à regarder en priorité :

  • type : c’est la qualité d’accès (du pire ALL au meilleur const). Un ALL sur une table à 5M lignes, c’est un incendie.
  • key vs possible_keys : possible_keys dit “ce que l’optimiseur pourrait utiliser”, key dit “ce qu’il utilise vraiment”. Si possible_keys est vide, vous avez probablement oublié un index (ou une condition non sargable).
  • rows et filtered : rows est une estimation du nombre de lignes lues, filtered le pourcentage retenu. Multipliez-les mentalement pour estimer les lignes “qui passent” et celles “jetées”.
  • Extra : c’est ici que vous voyez Using temporary, Using filesort, Using index (couvert), Using index condition (ICP), Using join buffer.

Pour aller plus loin sans “sur-interpréter”, gardez en tête deux pièges :

  1. EXPLAIN (sans ANALYZE) = estimations. Il peut être “logique” mais faux si les stats sont mauvaises.
  2. Le plan n’est pas stable : un changement de cardinalité (nouvelle boutique, import, suppression de données, bascule sur un clone) peut faire changer l’ordre des tables, l’index choisi, et donc le temps.

Un exemple minimal d’anti-pattern qu’on repère vite dans Extra :

  • Using temporary; Using filesort sur une requête de listing BO (commandes/clients/produits),
  • + rows très élevé,
  • + type=ALL sur la table principale (ou pire : sur une table jointe exécutée N fois).

Dans ce cas, une bonne routine de lecture est :

(1) vérifier ce qui filtre vraiment (WHERE) → (2) vérifier ce qui joint (JOIN ON) → (3) vérifier ce qui trie (ORDER BY) → (4) comparer ça avec les index existants (SHOW INDEX FROM ...).

Piège classique en PrestaShop : valider un plan “pas trop mauvais” sur une base de dev vide. Sur une prod, l’optimiseur change de plan dès que les stats et la cardinalité changent. En pratique : exécutez vos tests sur un dump prod anonymisé, et revalidez après ANALYZE TABLE (sinon les stats peuvent être obsolètes). Les outils “app” (profiling Symfony, logs) vous aident à repérer les requêtes ; mais pour détecter les effets N+1 et la dérive de mémoire côté Symfony, vous pouvez croiser avec l’approche instrumentation décrite dans Scout Monitoring Symfony : détection N+1 Doctrine et memory bloat.

Astuce pratique (sans changer de code) : si vous suspectez des index inutiles ou au contraire “indispensables”, MySQL 8 propose les index invisibles pour tester l’impact sans drop immédiat. Exemple :

ALTER TABLE ps_orders ALTER INDEX idx_shop_state_date INVISIBLE;
-- tests / A/B / observation sur une réplique
ALTER TABLE ps_orders ALTER INDEX idx_shop_state_date VISIBLE;

Cela permet de valider qu’un index sert vraiment au plan (ou qu’il a été remplacé par un meilleur composite), avant de le supprimer.

Optimiser WHERE : prédicats sargables, ordre des colonnes, index composites

Pour que MySQL exploite un index, vos conditions doivent être sargables (Search ARGument ABLE) : comparer une colonne à une constante (ou une plage) sans la “casser” avec une fonction. Exemple classique à bannir : WHERE DATE(o.date_add) = '2026-05-25' (fonction sur la colonne) → index inutilisable. Préférez WHERE o.date_add >= '2026-05-25' AND o.date_add < '2026-05-26'. Même combat pour LOWER(email), CAST(), CONCAT(), etc. Si vous n’avez pas le choix, la stratégie moderne est la colonne générée + index (MySQL 8) : email_lc GENERATED ALWAYS AS (LOWER(email)) STORED puis index dessus.

Deux autres cas très fréquents en e-commerce :

  • LIKE '%abc%' : non sargable (l’index B-Tree ne peut pas démarrer au milieu). Si vous pouvez, préférez LIKE 'abc%' (préfixe) ou une solution de recherche dédiée (fulltext / moteur externe).
  • OR sur plusieurs colonnes : MySQL peut parfois faire un “index merge”, mais c’est souvent moins bon qu’une réécriture en UNION ALL (à valider avec EXPLAIN ANALYZE), surtout si chaque branche peut utiliser un bon index.

Sur les index composites, retenez la règle “leftmost prefix” : MySQL exploite efficacement un index composite à partir de ses colonnes de tête, tant que les prédicats suivent une logique compatible (égalité d’abord, puis range). La ressource Use The Index, Luke! détaille bien l’importance de l’ordre des colonnes pour les index multi-colonnes : Use The Index, Luke! (chapitres sur les index composites).

Concrètement, si votre requête filtre toujours par id_shop et active puis fait un range sur date_add, un index (id_shop, active, date_add) est cohérent ; l’inverse (date_add, id_shop, active) l’est nettement moins.

Exemple (catalogue) simplifié proche d’un listing back-office :

SELECT p.id_product
FROM ps_product p
WHERE p.id_shop_default = 1
  AND p.active = 1
  AND p.date_add >= '2026-01-01'
ORDER BY p.date_add DESC
LIMIT 50;

Sans index composite adapté, vous verrez souvent type=ALL ou un index partiel puis Using filesort. Avec un index ciblé :

ALTER TABLE ps_product
  ADD INDEX idx_shop_active_date (id_shop_default, active, date_add, id_product);

Vous donnez à MySQL la possibilité de filtrer (id_shop_default, active), de parcourir la plage (date_add) et de retourner id_product sans lookup additionnel (selon le SELECT). Ce dernier point est un index couvrant : dans Extra, vous espérez voir Using index (ce n’est pas “magique”, mais c’est souvent un gros gain sur les tables chaudes).

Checklist rapide avant d’ajouter un index “WHERE” :

  • Les colonnes du WHERE sont-elles dans le même type que les valeurs comparées (pas de conversion implicite) ?
  • Les filtres structurants (id_shop, id_lang) sont-ils en tête des index composites quand ils sont quasi systématiques ?
  • Le WHERE contient-il une fonction sur la colonne (DATE, LOWER, etc.) ?
  • L’index proposé améliore-t-il aussi ORDER BY ou JOIN (idéalement un index sert plusieurs requêtes clés) ?

Optimiser JOIN : indexer les colonnes de jointure, contrôler la cardinalité, éviter les plans absurdes

MySQL exécute majoritairement les JOIN via nested-loop join. Traduction opérationnelle : pour chaque ligne de la table A (driving table), il va chercher les correspondances dans B via un index. Si la colonne de jointure n’est pas indexée côté B, vous forcez un balayage (ALL) répété, ou un join buffer (avec Using join buffer) qui ne sauvera pas une mauvaise modélisation. Sur PrestaShop, ça se produit souvent avec des tables de modules (log, mapping, feed, tables de synchronisation) où l’index sur id_product, id_order, id_customer a été “oublié”.

Deuxième point, moins glamour mais fréquent : mismatch de types/collations. Joindre INT sur VARCHAR, ou utf8mb4_general_ci sur utf8mb4_unicode_ci, pousse MySQL à convertir à la volée et peut rendre l’index inutilisable. En multi-boutique/multi-langue, vérifiez aussi que vos colonnes id_lang, id_shop sont bien du même type partout, et que les index composites tiennent compte des filtres structurels (id_shop, id_lang) avant les colonnes plus volatiles.

Cas typique côté commandes (liste BO) :

SELECT o.id_order, o.date_add, c.email
FROM ps_orders o
JOIN ps_customer c ON c.id_customer = o.id_customer
WHERE o.id_shop = 1
  AND o.current_state IN (2,3,4)
ORDER BY o.date_add DESC
LIMIT 100;

Vous voulez au minimum :

  • un index sur ps_orders(id_shop, current_state, date_add, id_customer) (ou variante selon les filtres réels),
  • ps_customer doit évidemment avoir un index (PK) sur id_customer.

Deux éléments à valider avec EXPLAIN / EXPLAIN ANALYZE :

  1. Ordre des tables : si MySQL commence par une table très large sans filtre sélectif, vous aurez beaucoup de lookups inutiles.
  2. Cardinalité : un bon index sur une colonne à très faible cardinalité (ex. active tout seul) n’est pas très utile ; il devient utile quand il est combiné à une colonne plus sélective (ex. id_shop, date_add, id_customer…).

Mini-scenario “module” fréquent : un module ajoute une table ps_module_sync (idproduct, idshop, status, updated_at) et joint dessus pour filtrer un export. Si l’index est (status) uniquement, la requête peut lire une grosse partie de la table avant de rejoindre. Un index (id_shop, status, updated_at, id_product) est souvent plus proche du réel : on filtre par boutique, état, et on parcourt par date.

Et si vous observez des déclinaisons N+1 (ex. une requête par commande pour récupérer un champ annexe), ne mettez pas un index pour cacher un défaut : regroupez la requête, ou corrigez la couche applicative. Sur PrestaShop 9 (Symfony 6.4), l’écosystème bouge sur la perf et l’API ; à relire pour le contexte global : PrestaShop 9 : nouveautés techniques Symfony 6.4, API et performances.

Optimiser ORDER BY : éviter filesort/temporary, indexer pour le tri + LIMIT, pagination stable

ORDER BY est l’endroit où les requêtes PrestaShop s’effondrent, surtout avec LIMIT et des offsets. Dans EXPLAIN, le marqueur Using filesort signifie “MySQL doit trier”, pas “il va forcément exploser”. Mais si filesort s’applique à des centaines de milliers de lignes avec Using temporary, vous allez saturer CPU/IO et provoquer de la latence globale. L’objectif est de permettre un scan d’index dans l’ordre demandé, ce qui supprime (ou réduit fortement) le tri.

Règle opérationnelle : pour que l’index serve au tri, le ORDER BY doit correspondre à l’ordre des colonnes de l’index (et à la direction, même si MySQL 8 sait mieux gérer les parcours inverses). Si vous filtrez par (id_shop, active) puis triez par date_add DESC, un index (id_shop, active, date_add DESC) est logique. Si vous triez par une expression (ORDER BY RAND(), ORDER BY price*tax_rate), oubliez l’index : vous paierez le tri. Dans ces cas, changez la stratégie (pré-calcul, table de ranking, cache applicatif, ou tri côté moteur de recherche dédié).

Deux détails importants (souvent sous-estimés) :

  • Le tri + LIMIT est un super pouvoir si l’index correspond : MySQL peut s’arrêter après N lignes sans trier tout le monde.
  • Le tri devient coûteux quand il “embarque” des colonnes larges (beaucoup de champs sélectionnés, ou des TEXT). D’où l’intérêt des requêtes en 2 temps : d’abord récupérer les identifiants triés (via index), puis charger le détail via un IN (...) ou un join sur une sous-requête (à manier proprement).

La pagination est l’autre tueur : LIMIT 50 OFFSET 50000 force MySQL à parcourir/ignorer 50k lignes avant d’en rendre 50. Sur des listes produits/commandes, la solution robuste est la keyset pagination (aussi appelée “seek method”) : vous paginez avec la dernière valeur triée, typiquement (date_add, id_order) ou (id_product).

-- page suivante
WHERE (o.date_add, o.id_order) < ('2026-05-25 10:30:00', 123456)
ORDER BY o.date_add DESC, o.id_order DESC
LIMIT 50;

Avec un index (id_shop, current_state, date_add DESC, id_order DESC), vous obtenez une pagination stable, rapide, et surtout prévisible.

Résumé (choix de pagination) :

Méthode Avantage Limite
LIMIT ... OFFSET ... Simple à implémenter Coût qui augmente linéairement avec l’offset
Keyset pagination Coût stable, très rapide Nécessite un ordre strict + un “curseur” (dernière clé)

Et si vous couplez ça à du cache fragmenté côté HTTP (catalogue, blocs), vous évitez de re-taper la DB pour des pages quasi identiques ; sur ce volet, voir Varnish Cache : configuration ESI pour optimiser le cache par fragments.

Déployer des index en prod sans casser : mesure, migrations, verrous, rollback

Ajouter un index n’est pas une opération “SQL anodine” en prod. Selon la version MySQL/MariaDB, la taille de table, et le type d’ALTER, vous pouvez déclencher un rebuild complet avec lock long. MySQL 8 propose des algorithmes plus souples (ALGORITHM=INPLACE / INSTANT selon cas), mais ce n’est pas universel. La discipline minimale : mesurer d’abord (slow query log, performance_schema, APM), isoler les 5–10 requêtes dominantes, puis tester l’index sur une réplique ou un dump prod. Évitez de poser des index “au hasard” parce que EXPLAIN a affiché ALL une fois.

Un déroulé pragmatique (qui tient même sur des boutiques à forte charge) :

  1. Identifier la requête (slow log / APM / logs Symfony) + son contexte (page BO, cron, webhook, API).
  2. Reproduire sur un dataset réaliste.
  3. Mesurer avant : EXPLAIN ANALYZE + temps au 95e percentile si possible (pas juste “un run”).
  4. Proposer l’index (ou la réécriture) en s’assurant qu’il sert WHERE/JOIN/ORDER BY plutôt qu’un seul aspect.
  5. Mesurer après sur une réplique.
  6. Déployer avec stratégie de lock maîtrisée + plan de rollback.

Sur des tables très grosses (ex. ps_order_detail, logs de modules), privilégiez des outils de migration online si vous n’avez pas de fenêtre de maintenance : pt-online-schema-change (Percona Toolkit) ou équivalent, ou une stratégie “shadow table”. Documentation (référence externe utile) : pt-online-schema-change — Percona Toolkit

Le point important : l’index doit être validé par EXPLAIN ANALYZE (temps réel), pas seulement par EXPLAIN (estimations). Dans beaucoup de cas, on observe un gain net quand on élimine un tri et qu’on réduit drastiquement les lignes lues.

Autre levier “propre” : détecter les index inutilisés avant d’empiler des index correctifs. Sur MySQL 8, la vue sys.schema_unused_indexes (sys schema) peut aider à repérer des index jamais utilisés (attention : il faut que performance_schema collecte correctement, et la conclusion doit rester prudente sur des charges saisonnières).

Côté développement de modules PrestaShop, le bon pattern est d’installer vos index dans le script d’installation du module, de manière idempotente (vérifier existence), et de documenter le coût. Exemple (approche brute) : ALTER TABLE {$prefix}ma_table ADD INDEX idx_x (col1, col2);. Prenez en compte le multi-boutique (id_shop) et la multi-langue (id_lang) dès le design, sinon vous finirez par empiler des index correctifs. Et après déploiement : ANALYZE TABLE sur les tables touchées pour recalculer les stats et stabiliser les plans. Pour la gouvernance et la qualité (tests, CI/CD, migrations contrôlées), le cadre décrit dans Développement PHP critique : architecture DDD, tests et CI/CD s’applique très bien aux changements DB : une migration sans plan de rollback est une dette.

Références externes (documentation/ouvrages) :

À lire aussi