Index MySQL : composer, mesurer avec EXPLAIN et optimiser les requêtes

Table des matières :

Sur une boutique PrestaShop (8.1 à 9.2) avec MySQL 8.0+ ou MariaDB 10.6+, la performance « base de données » est presque toujours une histoire d’index MySQL mal composés (ou absents), de requêtes non sargables, et d’optimiseur qui fait ce qu’il peut avec des statistiques fausses. Le cœur de PrestaShop n’aide pas toujours : requêtes générées par la couche legacy, filtres/facettes, listes BO triées sur des colonnes non indexées, multi-boutique qui multiplie les prédicats id_shop, etc.

L’objectif ici est pragmatique : composer des index (au sens « index composites », rien à voir avec PHP Composer), mesurer avec EXPLAIN / EXPLAIN ANALYZE, puis optimiser les requêtes sans casser la prod.

Avant de toucher au schéma, gardez en tête deux réalités très “terrain” sur l’e‑commerce :

Les pics de charge (soldes, Black Friday, périodes de Noël) font exploser simultanément lecture catalogue (front) et écritures (checkout). Un index qui “accélère un listing” mais ralentit une écriture critique peut coûter plus cher qu’il ne rapporte.
En multi-boutique / multi-langue, la cardinalité change : id_shop et id_lang sont souvent des prédicats d’égalité quasi obligatoires, donc l’indexation doit les intégrer intelligemment au lieu de les “ajouter au hasard”.

Composer un index MySQL (composite) : règles qui évitent les faux gains

Un index MySQL est une structure de données qui accélère la localisation des lignes (au prix d’un surcoût en écriture et en stockage). Si vous ne regardez pas comment la requête est exécutée, vous « optimisez » à l’aveugle : EXPLAIN fournit des informations sur l’exécution des statements et doit être la base de vos décisions.

Sur InnoDB, il faut intégrer une contrainte souvent oubliée : les index secondaires contiennent la clé primaire (pour pouvoir revenir au clustered index). Conséquence directe : ajouter un index sur une table volumineuse PrestaShop (ex. ps_orders, ps_order_detail, ps_product_shop) augmente vite la taille disque et la pression I/O. Autre conséquence : une clé primaire « large » (UUID, composites, etc.) pénalise tous les index secondaires. Dans PrestaShop, les PK sont généralement des INT auto-incrémentés, ce qui limite la casse — mais les modules peuvent introduire des schémas moins propres.

Un index composite (multi-colonnes) se raisonne avec trois règles :

Règle du préfixe gauche (leftmost prefix) : un index multi-colonnes peut être utilisé sur toutes les colonnes de l’index ou sur son préfixe gauche.
Ordre des colonnes = ordre des prédicats les plus sélectifs ET compatibles avec les accès : dans la pratique, on place souvent d’abord les colonnes d’égalité (= / IN (...)), ensuite les colonnes de range (>, <, BETWEEN), et on finit par celles d’ORDER BY si on veut éviter filesort.
Un index sert une requête, pas une table : indexer « parce que ça semble utile » est la meilleure façon d’augmenter les temps d’INSERT/UPDATE, le temps de rebuild, et le lag de réplication.

Deux points qui évitent beaucoup d’index “placebo” sur PrestaShop :

Colonnes peu sélectives : active=1, deleted=0, visibility='both' sont souvent peu sélectives. Ils peuvent faire partie d’un index composite (parce qu’ils arrivent “gratuitement” après une colonne très sélective), mais un index qui commence par une colonne à faible sélectivité est souvent un mauvais pari.
Index redondants : (id_shop, id_product) et (id_shop, id_product, active) peuvent être utiles dans des cas précis, mais trop souvent on empile des variantes quasi identiques. Une bonne hygiène consiste à lister les requêtes critiques (top N) puis à viser le minimum d’index qui les couvrent.

Mini-table de décision (très utilisée en audit) :

Objectif	Modèle de requête	Index composite typique	Risque fréquent
Filtrer + joindre	`WHERE a=? AND b=? JOIN ...`	`(a, b, join_key)`	oublier `join_key` → lookup coûteux
Filtrer + range	`WHERE a=? AND date BETWEEN...`	`(a, date)`	mettre `date` avant `a` → range trop large
Filtrer + trier + limiter	`WHERE a=? ORDER BY x DESC LIMIT n`	`(a, x)`	`filesort` si index incompatible
Multi-shop / multi-lang	`WHERE id_shop=? AND id_lang=? ...`	`(id_shop, id_lang, ...)`	`id_shop` en dernier → scan inutile

SELECT p.id_product
FROM ps_product_shop ps
JOIN ps_product p ON p.id_product = ps.id_product
WHERE ps.id_shop = 1
  AND ps.active = 1
ORDER BY p.date_upd DESC
LIMIT 50;

Selon vos volumes, un index côté ps_product_shop du type (id_shop, active, id_product) aide au filtrage/JOINS, et côté ps_product un index (date_upd, id_product) peut aider au tri (ou vous pousse à réécrire la requête pour trier sur une colonne déjà disponible via ps_product_shop, si possible).

Deux remarques importantes sur cet exemple :

Si vous récupérez ensuite des champs de p (pas seulement p.id_product), l’index sur ps_product ne “couvre” pas la requête et MySQL fera quand même des accès à la table (ce qui est normal). L’objectif ici est surtout de rendre le tri bon marché et d’éviter un gros filesort.
En pratique, vous pouvez parfois inverser l’approche : sélectionner d’abord les id_product pertinents côté ps_product_shop, limiter, puis joindre ps_product. Cette réécriture réduit le volume trié.

Le point clé : ne créez pas deux index “presque pareils” ; choisissez celui qui couvre le plus de requêtes critiques (front, BO, cron, import), et documentez le “pourquoi” (ordre des colonnes, sélectivité, plan observé).

Mesurer correctement avec EXPLAIN / EXPLAIN ANALYZE (et arrêter de “tuner à l’instinct”)

EXPLAIN vous donne une estimation (plan, type d’accès, index choisi, lignes estimées). EXPLAIN ANALYZE (MySQL ≥ 8.0.18 ; MariaDB propose aussi EXPLAIN ANALYZE) ajoute des métriques runtime (timings, loops) et évite une partie des illusions dues aux estimations. En e-commerce, la différence est énorme : une estimation rows=100 peut masquer un accès réel à 300 000 lignes si les stats sont mauvaises, ou si la distribution des valeurs est très asymétrique (typiquement : une poignée de produits “stars” présents dans énormément de lignes).

Sur MySQL, commencez simple :

EXPLAIN
SELECT od.id_order, SUM(od.total_price_tax_incl)
FROM ps_order_detail od
WHERE od.product_id = 123
  AND od.id_shop = 1
GROUP BY od.id_order
ORDER BY od.id_order DESC
LIMIT 50;

Ce que vous lisez en priorité :

type (ordre grossier : system/const > eq_ref > ref > range > index > ALL). Si vous voyez ALL sur une grosse table, vous avez un problème d’index ou de sargabilité.
key et key_len : l’index réellement utilisé et sa longueur exploitée. key vide = pas d’index (ou index jugé non rentable).
rows et filtered : estimation de lignes lues et pourcentage conservé. Mauvaises stats => mauvaises estimations => mauvais choix d’index.
Extra : cherchez les drapeaux rouges : Using temporary, Using filesort, Using join buffer.

À ce stade, une erreur courante est de ne regarder que key=... et de conclure trop vite. Deux compléments rapides, très utiles en audit :

possible_keys : si l’index que vous venez d’ajouter n’apparaît même pas dans possible_keys, la requête ne “matche” pas sa forme (ordre des colonnes, type de comparaison, collations, fonctions sur colonne…).
La combinaison rows × loops (dans EXPLAIN ANALYZE) : une boucle sur un nested loop join qui tourne 50 000 fois est souvent le vrai problème, même si chaque itération “semble” légère.

Ensuite, passez en mode “preuve” :

EXPLAIN ANALYZE
SELECT od.id_order, SUM(od.total_price_tax_incl)
FROM ps_order_detail od
WHERE od.product_id = 123
  AND od.id_shop = 1
GROUP BY od.id_order
ORDER BY od.id_order DESC
LIMIT 50;

Si EXPLAIN ANALYZE vous montre des temps sur un nœud table scan ou un filesort, ne sautez pas directement à “ajouter un index”. Vérifiez d’abord :

1) Statistiques : ANALYZE TABLE ps_order_detail; (à exécuter hors pic) et, sur MySQL 8, l’usage d’histogrammes peut stabiliser l’optimiseur sur des colonnes à distribution atypique. Concrètement, un histogramme est pertinent quand une valeur apparaît énormément plus que les autres (par exemple un id_shop principal, ou un current_state dominant).

2) Paramètres et engine : buffer pool saturé, tmp_table_size trop faible, ou contention I/O peuvent faire “sembler” une requête lente alors que le plan est correct. Dans ce cas, l’indexation n’est pas le bon levier : vous êtes face à un problème de capacité (RAM/IOPS) ou de concurrence.

3) Observabilité : activez le slow query log et corrélez avec une supervision. Pour standardiser les métriques côté infra (CPU, I/O, latence, buffer pool), vous pouvez vous appuyer sur ce guide de supervision : Netdata et collecteurs (Docker/Kubernetes/AWS).

Astuce très pratique en production (MySQL 8) quand vous suspectez un index de “faire plus de mal que de bien” : les index invisibles. Vous pouvez rendre un index invisible pour voir si l’optimiseur s’en passera (ou si le plan devient catastrophique) sans le supprimer immédiatement. C’est une excellente étape “pré-rollback” quand on veut nettoyer des index hérités de modules.

Optimiser la requête avant l’index (sargabilité, JOIN, ORDER BY, pagination)

Un index ne sauve pas une requête non sargable. Les patterns qui tuent l’utilisation d’index sur MySQL/MariaDB : fonctions sur colonne (WHERE DATE(date_add)=...), cast implicite, LIKE '%suffix', ou conditions qui empêchent l’utilisation du préfixe. Exemple classique en BO : filtrer par date avec DATE() au lieu d’un range.

-- Mauvais (non sargable)
WHERE DATE(o.date_add) = '2026-08-25'

-- Bon (range sargable)
WHERE o.date_add >= '2026-08-25 00:00:00'
  AND o.date_add <  '2026-08-26 00:00:00'

Autres “anti-patterns” fréquemment vus dans des modules PrestaShop :

WHERE IFNULL(col, 0) = 0 → remplacez par WHERE col IS NULL OR col = 0 (et réfléchissez à la modélisation).
WHERE col + 0 = 123 (cast implicite) → assurez-vous que col est du bon type et comparez sans transformation.
WHERE col LIKE '%abc%' → si c’est de la recherche, MySQL relationnel n’est pas toujours la meilleure réponse (voir section dédiée plus bas).

Deuxième levier : réduire le coût des lectures. En MySQL, un index secondaire oblige souvent à faire un “back to table” (lookup sur le clustered index) si la requête lit des colonnes non couvertes. Un covering index (index qui contient toutes les colonnes nécessaires) peut réduire drastiquement les I/O. Vous le voyez dans EXPLAIN via Extra: Using index (attention : ça ne veut pas dire “l’index est bon”, ça veut dire “index-only access”).

Sur PrestaShop, beaucoup de requêtes front lisent “trop” de colonnes (SELECT p.*), ce qui empêche de bénéficier d’index couvrants. Si vous contrôlez la requête (module, override, repository Symfony), listez les colonnes. Mini-scénario typique :

Vous avez une page catégorie qui ne montre que id_product, price, link_rewrite, cover.
Si le module fait SELECT p.*, pl.*, i.* ..., il force MySQL à lire beaucoup plus de pages de données.
En réduisant la projection (colonnes sélectionnées), vous diminuez mécaniquement les lectures, et certains index deviennent “suffisants” sans ajout.

Troisième levier : ORDER BY + LIMIT + pagination. La pagination en LIMIT offset, n devient rapidement O(n) avec un offset grand (MySQL doit “avancer” dans le tri/scan). À partir d’une certaine volumétrie (listes commandes, clients, logs), basculez sur une keyset pagination (aussi appelée “seek method »):

-- Page suivante : on se base sur la dernière clé vue
SELECT o.id_order, o.date_add
FROM ps_orders o
WHERE o.id_shop = 1
  AND o.id_order < :last_id
ORDER BY o.id_order DESC
LIMIT 50;

Avec un index (id_shop, id_order) (ou un index équivalent exploitant l’ordre), vous supprimez une grande partie des tris et des lectures inutiles. C’est typiquement plus rentable qu’empiler des index.

Enfin, surveillez les OR : un WHERE a=? OR b=? empêche souvent un bon usage d’un index composite unique. Selon le cas, réécrire en UNION ALL (deux requêtes chacune bien indexée) peut être plus stable — à valider avec EXPLAIN ANALYZE, pas à l’intuition.

Déployer un nouvel index en production : DDL online, risques InnoDB et plan de rollback

Créer un index sur une table chaude n’est pas une “petite modif”. Même avec les DDL online (MySQL 8) ou les capacités InnoDB modernes, vous payez au minimum : scan complet de la table, génération de l’arbre B-Tree, pression sur le buffer pool, et potentiellement du lag de réplication. Sur une boutique à trafic, l’effet secondaire le plus fréquent est un TTFB qui explose (temps serveur) parce que MySQL monopolise l’I/O.

Si vous êtes sur MySQL 8, validez le mode DDL :

ALTER TABLE ps_orders
  ADD INDEX idx_shop_date (id_shop, date_add),
  ALGORITHM=INPLACE,
  LOCK=NONE;

Mais ne partez pas du principe que LOCK=NONE = “aucun impact”. Sur gros volumes, l’impact se voit sur le temps de réponse global, et les sessions concurrentes. MariaDB a aussi ses particularités (différences d’implémentation et de planning DDL).

Deux tactiques “safe” (souvent sous-utilisées) :

Créer l’index en période creuse + limiter la concurrence côté application (par exemple en évitant imports/exports simultanés, ou en réduisant temporairement certains crons).
Tester l’index en “mode prudent” : sur MySQL 8, créer un index puis le passer visible/invisible vous permet de comparer des plans et d’éviter un “grand ménage” irréversible trop vite.

Sur des tables réellement massives (ex. ps_connections, ps_log, ps_order_detail avec historique long), la stratégie la plus safe reste un outil de schema change online type pt-online-schema-change (Percona Toolkit) ou gh-ost (plutôt MySQL). Pour pt-online-schema-change, la documentation officielle est ici : documentation pt-online-schema-change (Percona Toolkit).

Plan minimal avant d’ajouter un index :

1) Staging avec données représentatives (dump anonymisé, mêmes cardinalités) + test de charge. Cette approche est détaillée dans : migration MySQL 5.7→8 : procédure, staging et tests de charge.

2) Mesure avant/après : latence p95/p99, CPU MySQL, read IOPS, Handler_read%, taille d’index, temps d’écriture. Sans métriques, vous ne savez pas si vous avez juste déplacé le problème (par exemple : moins de CPU, mais plus d’I/O et plus de latence).

3) Rollback : savoir supprimer l’index (DROP INDEX) et accepter le coût associé. Gardez aussi en tête que trop d’index dégradent les écritures (checkout, création commande, mise à jour stock). C’est précisément le genre de dette qui fait “PrestaShop lent” sans que le code change.

Cas concrets PrestaShop : catalogue, commandes, multiboutique et modules

Listing catalogue (front) : `product_shop`, `category_product`, état actif

Sur un catalogue, les requêtes critiques combinent souvent id_category, id_shop, active, visibility, puis un tri (position, prix, date). Exemple (simplifié) :

SELECT cp.id_product
FROM ps_category_product cp
JOIN ps_product_shop ps ON ps.id_product = cp.id_product
WHERE cp.id_category = 12
  AND ps.id_shop = 1
  AND ps.active = 1
ORDER BY cp.position ASC
LIMIT 60;

Index candidats :

Sur ps_category_product : (id_category, position, id_product) si vous triez par position et joignez sur id_product. Alternative : (id_category, id_product) si la position est peu utilisée.
Sur ps_product_shop : (id_shop, active, id_product) pour filtrer vite et joindre.

Le piège : ajouter un index “fourre-tout” (id_shop, active, visibility, id_category, ...) sur la mauvaise table. Un index composite efficace suit la requête : prédicats de la table, puis colonnes d’ordre.

Astuce PrestaShop spécifique : si vous êtes en multi-langue et que la requête va chercher des libellés dans ps_product_lang, un index (id_shop, id_lang, id_product) (ou (id_lang, id_shop, id_product) selon vos prédicats dominants) est souvent plus utile qu’un index “à l’envers”, parce que id_lang est presque toujours en égalité et doit éviter un scan de toute une langue.

Si vous avez déjà une stratégie de performance globale, recoupez avec ce guide de performance PrestaShop (cache, WebP, base MySQL) pour éviter de traiter la base comme seul levier.

Listes commandes BO : filtres par boutique + date + état

Les écrans BO (commandes, paniers, clients) sont souvent les pires parce qu’ils combinent filtres, tri, et jointures, tout en étant peu cacheables. Une requête typique sur ps_orders :

SELECT o.id_order, o.reference, o.date_add, o.current_state
FROM ps_orders o
WHERE o.id_shop = 1
  AND o.date_add >= '2026-08-01'
  AND o.date_add <  '2026-09-01'
  AND o.current_state IN (2,3,4)
ORDER BY o.date_add DESC
LIMIT 50;

Ici, un index (id_shop, date_add, current_state) ou (id_shop, current_state, date_add) dépend de votre sélectivité réelle (nombre d’états, distribution des commandes). EXPLAIN ANALYZE tranche :

Si le moteur parcourt un range énorme sur date_add puis filtre les états, inversez l’ordre.
Si au contraire current_state est très peu sélectif (ex. “payé” = majorité), mettre l’état trop tôt peut dégrader.

Deux points pratiques souvent oubliés dans le BO :

Tri et filtre sur reference : si votre support cherche fréquemment “par référence”, un index séparé sur reference est souvent indispensable, car c’est un accès pointé très fréquent (et très visible côté UX).
Pagination : les écrans BO peuvent atteindre des offsets énormes (ex. page 200). La keyset pagination (sur id_order ou date_add) est un énorme levier… mais elle nécessite parfois un ajustement côté requête générée (override/module).

Le point d’architecture : si vos BO deviennent lents, ce n’est pas uniquement “un index manquant”. Vérifiez aussi la stratégie cache/OPcache/Redis et la pression globale côté serveur : stratégie cache PrestaShop (OPcache, Redis, Varnish, CDN). Une base saturée par manque d’I/O ne “sauvera” pas ses requêtes avec un index de plus.

Recherche et facettes : quand l’index MySQL n’est pas la bonne réponse

La recherche full-text “à la MySQL” et les facettes (notamment via ps_facetedsearch) ont des patterns qui dégénèrent : multiples jointures, agrégations, combinaisons, tri de pertinence, etc. À partir d’une volumétrie raisonnable, vous finissez par vous battre contre les limites du moteur relationnel pour un use-case de search.

Avant de bricoler 15 index, posez le diagnostic : est-ce une requête de lookup (relationnel) ou un moteur de recherche ? Si c’est de la recherche, la réponse technique propre est souvent un moteur dédié (Elasticsearch/OpenSearch/Meilisearch) avec synchronisation. Cet article explique bien l’approche : accélérer la recherche et les facettes catalogue avec Elasticsearch. L’intérêt est double : index inversé adapté + pression réduite sur MySQL (donc checkout plus stable).

Si vous restez sur MySQL/MariaDB, limitez la casse :

indexez les colonnes de jonction (id_product, id_attribute, id_shop, id_lang) en respectant le préfixe gauche ;
surveillez l’impact sur les écritures (mise à jour stock, import catalogue) ;
évitez de transformer MySQL en moteur de recherche via des LIKE '%...%' non bornés, qui forcent des scans.

Et surtout, gardez un œil sur la surface d’attaque et les modules : une base “optimisée” mais exposée via une mauvaise config reste une base fragile. Voir : sécurité MySQL (pare-feu, TLS, moindre privilège, audit).

Checklist de validation : index MySQL utile, ou dette technique déguisée

Pour décider si un index est “bon”, ne vous contentez pas de gagner 30 ms sur une requête en isolation. Dans un e-commerce, l’indicateur business est souvent le checkout (écritures), et l’indicateur infra est la stabilité (I/O, réplication, verrous). Un index qui améliore un listing BO mais dégrade les INSERT de commandes est un mauvais trade-off.

Checklist opérationnelle (à appliquer sur staging puis prod) :

Capturer la requête réelle (log applicatif, slow log) + paramètres typiques (mêmes id_shop, mêmes périodes de dates, mêmes tailles de panier).
EXPLAIN puis EXPLAIN ANALYZE : vérifier accès (ref/range), absence de ALL, et surtout réduire Using temporary / Using filesort quand c’est pertinent.
Contrôler la “shape” : pas de fonctions sur colonnes filtrées, pas de casts implicites, pas de SELECT * quand vous pouvez sélectionner seulement les champs utiles.
Mesurer avant/après : p95/p99, CPU, read/write IOPS, taille d’index, temps d’écriture (import, checkout), lag replication.
Vérifier les statistiques (ANALYZE TABLE) et, si besoin, histogrammes (MySQL 8) avant de conclure.
Documenter : requêtes couvertes, raison de l’ordre des colonnes, coût attendu, procédure de rollback (et idéalement qui est responsable si la perf se dégrade).

Enfin, gardez une cohérence de stratégie : un index MySQL est un outil parmi d’autres. Sur PrestaShop, les gains durables viennent presque toujours d’un mix : nettoyage base et requêtes, caching applicatif (Redis), réduction des pages non cacheables, et éventuellement externalisation de la recherche. Pour une démarche d’audit structurée côté SQL, voir : audit MySQL/MariaDB orienté e‑commerce (requêtes lentes). L’indexation ne doit pas servir à compenser une requête mal écrite ou un module qui tire le schéma dans le mur.