PrestaShop MySQL : analyser slow query log et optimiser index

Table des matières :

1. Prérequis, périmètre et garde-fous (PrestaShop 8/9, MySQL 8.x / MariaDB)
2. Activer le slow query log correctement (sans générer du bruit inutile)
3. Exploiter le slow query log : prioriser par impact, pas par ego
4. Relier une slow query à un écran PrestaShop (et la reproduire proprement)
5. Passer du constat à l’action : lire EXPLAIN et concevoir un index utile (pas décoratif)
6. Déployer (et retirer) des index en production PrestaShop : procédure sûre et cas d’usage typiques

Prérequis, périmètre et garde-fous (PrestaShop 8/9, MySQL 8.x / MariaDB)

Les méthodes ci-dessous sont valables pour PrestaShop 1.7.x, 8.x et 9.x (back-office Symfony inclus), avec un socle InnoDB. Les exemples sont pensés pour MySQL 8.0 LTS et MariaDB 10.11 LTS (les deux restent très courants en 2026). Côté PHP, ça colle avec les stacks actuelles (PHP 8.2/8.3). Si vous êtes encore sur MySQL 5.7, vous pouvez activer le slow query log, mais vous perdez des outils précieux (notamment EXPLAIN ANALYZE) et vous cumulez de la dette d’exploitation : planifiez une migration (voir prérequis MySQL/MariaDB + collation : Prérequis système + migration MySQL → MariaDB/InnoDB + collation UTF-8).

Pré-requis opérationnels non négociables : (1) un accès suffisant pour modifier la configuration MySQL/MariaDB (root/DBA, ou au minimum SUPER/SYSTEM_VARIABLES_ADMIN selon version), (2) une rotation de logs (sinon vous allez remplir le disque), (3) un environnement de pré-production ou une réplique de lecture pour valider les index. Ajouter un index sur une table ps_orders ou une table “facettes” de plusieurs dizaines de millions de lignes en production sans stratégie DDL en ligne, c’est la recette pour un lock et une boutique KO.

Quelques garde-fous supplémentaires, très concrets en contexte PrestaShop :

• InnoDB uniquement : si vous avez encore des tables MyISAM “héritées” (rare, mais vu sur de vieux modules), elles n’ont pas les mêmes mécanismes de verrous et faussent votre diagnostic.
• Collation/charset homogènes : mélange de utf8mb4_general_ci / utf8mb4_unicode_ci / utf8mb4_0900_ai_ci = risques de conversions implicites, donc index non utilisés sur certains JOIN/WHERE. Ce symptôme ressemble à “MySQL est lent”, alors que c’est souvent “le plan ne peut pas utiliser l’index”.
• Même fuseau horaire dans vos logs : en Europe (et typiquement en France), vous corrélez souvent Nginx/Apache, PHP-FPM et MySQL. Or MySQL enregistre généralement en UTC selon la variable log_timestamps. Décidez tôt si vous préférez UTC partout (souvent le plus propre) ou un alignement SYSTEM, mais soyez cohérent, sinon vous perdrez du temps à “recoller” les timelines, surtout pendant les pics (soldes / Black Friday / campagnes TV).

Enfin, définissez un “baseline” avant de toucher à quoi que ce soit : p95/p99 des temps de réponse (front + BO), CPU MySQL, IOPS disque, taille du buffer pool, et surtout temps total passé en DB par requête HTTP. Sans mesure, vous allez « optimiser » une requête qui ne pèse rien.

Checklist minimale (à capturer avant optimisation, puis après pour vérifier un gain réel) :

• côté HTTP : p95/p99 request_time, taux d’erreur 5xx, endpoints les plus lents (front, BO, API, webservice) ;
• côté MySQL : Threads_running, Handler_read% (symptômes de scans), latence disque, hit ratio buffer pool (InnoDB), volume de redo (pression d’écriture) ;
• côté application : nombre de requêtes SQL par page (un écran BO à 400 requêtes, même “rapides”, est souvent un problème structurel).

Si vous n’avez pas de chaîne de logs propre (MySQL + PHP-FPM + Nginx/Apache), commencez par ça : Monitoring & logs PrestaShop (PHP, MySQL, JS) + alertes.

Activer le slow query log correctement (sans générer du bruit inutile)

Le slow query log n’est pas un “mode debug”, c’est un mécanisme de collecte ciblé : il enregistre les requêtes dont le temps d’exécution dépasse long_query_time, et (si configuré) uniquement celles qui examinent au moins min_examined_row_limit lignes.

Implication directe : si vous laissez min_examined_row_limit=0 et long_query_time trop bas, vous allez logguer beaucoup de petites requêtes « normales », et masquer les vrais problèmes (requêtes rares mais catastrophiques, ou requêtes fréquentes un peu trop lentes). En e-commerce PrestaShop, une base saine est souvent long_query_time=0.5 (500 ms) + min_examined_row_limit entre 10k et 100k pour réduire le bruit. Ensuite, vous descendez temporairement à 0.1–0.2 s pendant une fenêtre de charge représentative (idéalement quand les utilisateurs font vraiment du checkout, pas à 3h du matin).

Deux réglages “qualité de vie” à ne pas oublier :

• log_output=FILE : évitez TABLE en production. Écrire dans mysql.slow_log ajoute de la charge… au serveur que vous essayez de diagnostiquer.
• Horodatage : si vous corrélez avec des logs web en heure locale, vérifiez log_timestamps (souvent UTC par défaut). Garder UTC partout est généralement le plus robuste (équipes multi-sites, changements été/hiver), mais l’important est d’être cohérent.

Sur MySQL 8.x, si vous avez la main, préférez SET PERSIST (persistant au redémarrage, plus propre que l’édition de fichier “à chaud”). Sur MariaDB, SET PERSIST n’est pas toujours disponible selon version/distribution : revenez à my.cnf/mysqld.cnf.

-- MySQL 8.x (exécuter en admin)
SET PERSIST slow_query_log = ON;
SET PERSIST log_output = 'FILE';
SET PERSIST slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET PERSIST long_query_time = 0.5;
SET PERSIST min_examined_row_limit = 10000;

-- Optionnel, à activer seulement pour une session de chasse au bug
-- SET PERSIST log_queries_not_using_indexes = ON;

Côté exploitation : protégez le fichier (permissions), et faites tourner le log (logrotate + FLUSH SLOW LOGS;). Exemple de rotation (à adapter à votre distro) :

/var/log/mysql/mysql-slow.log {
  daily
  rotate 7
  compress
  delaycompress
  missingok
  notifempty
  create 640 mysql adm
  sharedscripts
  postrotate
    /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf -e 'FLUSH SLOW LOGS;'
  endscript
}

Attention RGPD : le slow log peut contenir des valeurs de paramètres (emails, IDs client, tokens) selon vos requêtes. Traitez-le comme une donnée sensible : accès restreint, rétention courte, et pas de partage “à la va-vite” avec des prestataires. Dans un contexte UE, c’est typiquement le genre de fichier qui finit dans un ticket/support — évitez ce scénario en appliquant une hygiène simple (least privilege + purge).

Exploiter le slow query log : prioriser par impact, pas par ego

Une fois le log activé, l’objectif n’est pas de lire 10 000 lignes, mais de regrouper les requêtes par “empreinte” (fingerprint) et de prioriser par : (a) temps total cumulé (Query_time total), (b) fréquence, (c) ratio Rows_examined / Rows_sent, et (d) contention (Lock_time). Les lenteurs PrestaShop “qui font mal” sont souvent des requêtes moyennement lentes mais exécutées des milliers de fois (listings produits, règles panier, calcul prix spécifiques, modules qui hookent partout).

Pour une première passe, mysqldumpslow (fourni) suffit. Pour un vrai tri, pt-query-digest (Percona Toolkit) reste un standard de facto : il normalise les requêtes, calcule des percentiles et sort un top exploitable. Référence outil (documentation Percona Toolkit) : Documentation pt-query-digest (Percona Toolkit)

# Basique (MySQL/MariaDB)
mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log

# Plus sérieux (Percona Toolkit)
pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow-report.txt

# Variante : n'analyser qu'une fenêtre horaire si le log est massif
pt-query-digest --since '2026-07-31 10:00:00' --until '2026-07-31 12:00:00' /var/log/mysql/mysql-slow.log

Grille de lecture rapide (utile pour trier “quoi optimiser en premier” sans se raconter d’histoires) :

Signal dans le rapport	Interprétation probable	Action typique
Temps total cumulé très élevé, fréquence élevée	“taxe” permanente sur la boutique	index, réduction de cardinalité, cache, limiter appels
Rows_examined énorme, Rows_sent faible	scan inutile / index absent ou non utilisé	index composite, réécriture WHERE, collation/typage
Lock_time notable	contention / DDL / transactions longues	revoir transactions, verrous, batch, éviter DDL aux heures de pointe
Requêtes “simples” mais très fréquentes	N+1, hooks modules, template	profiler + réduire nombre de requêtes

Un pattern à repérer immédiatement : Rows_examined énorme pour Rows_sent minuscule (ex. 20 lignes retournées après avoir scanné 2M). En pratique, c’est typiquement : index absent, index non utilisé (mauvais ordre de colonnes dans un index composite), ou requête non-sargable (LIKE '%term%', fonctions sur colonne, casts implicites).

Astuce PrestaShop “terrain” : séparez, si possible, les périodes front (trafic client) et BO (ops, SAV, préparation commandes). Les requêtes qui tuent le BO ne sont pas forcément celles qui tuent le tunnel d’achat. Par exemple, les exports et grilles BO (commandes, clients, stats) déclenchent souvent des ORDER BY + filtres multi-colonnes, là où le front subit plutôt des jointures catalogue et du stock.

Pour une remise à niveau côté requêtes/index hors PrestaShop, gardez sous la main ce guide : Optimiser requêtes, schémas et performances MySQL en production.

Relier une slow query à un écran PrestaShop (et la reproduire proprement)

Le cœur PrestaShop n’ajoute pas, par défaut, de “tag” applicatif dans les requêtes SQL (commentaires, correlation-id). Résultat : le slow query log vous donne du SQL, mais pas “quelle page l’a déclenché”. La méthode la plus fiable côté BO consiste à reproduire depuis une page connue (ex. grille commandes, recherche catalogue), pendant que vous capturez les requêtes (slow log + access logs). Pour le front, vous pouvez aussi isoler par user-agent (curl) et par fenêtre horaire.

Pour rendre la corrélation beaucoup plus robuste, deux bonnes pratiques côté HTTP (Nginx/Apache) :

• logguez un identifiant de requête (ex. header X-Request-Id) et propagez-le si vous avez un reverse-proxy/CDN ;
• logguez des métriques de latence pertinentes ($request_time, et si vous avez PHP en upstream : $upstream_response_time) afin de savoir si la lenteur est côté PHP ou côté DB.

Si vous avez besoin d’un pont “code → SQL” pour une chasse courte, vous avez trois options pragmatiques :

1. Profiler Symfony (BO moderne) : vous récupérez les requêtes SQL et leur durée sur la requête HTTP concernée.
2. Trace PHP-FPM (slowlog) : stack trace côté PHP quand une requête HTTP dépasse un seuil, très utile pour tomber sur le module/hook responsable.
3. Instrumentation applicative (Blackfire / APM) : pas obligatoire, mais c’est souvent le seul moyen de distinguer “requête lente” vs “trop de requêtes”.

Sur ce point, une méthode complète (SQL inclus) est décrite ici : Diagnostiquer des lenteurs et des requêtes SQL côté code PrestaShop.

Mini-scenario typique (réaliste en exploitation) : votre équipe support en BO ouvre la grille commandes à 16h (heure locale) et “ça rame”. Dans le slow log, vous voyez une requête SELECT ... FROM ps_orders ... ORDER BY date_add DESC LIMIT 50; à 800 ms. Sans corrélation temporelle propre (UTC vs heure locale), vous la cherchez au mauvais moment dans les access logs et vous concluez à tort que “le problème est aléatoire”. D’où l’intérêt de cadrer fuseaux/horodatages dès le départ.

La reproduction doit être faite sur des données comparables (même volumétrie, mêmes index, même collation). Les plans d’exécution changent radicalement selon la cardinalité et les statistiques. Si vous “testez” une requête lente sur une base de dev de 5 000 produits, vous allez conclure à tort qu’un index est inutile. À minima, clonez la base et anonymisez si nécessaire.

Option utile (surtout sur MySQL 8) si vous voulez compléter le slow log : exploiter performance_schema pour obtenir un top de requêtes par digest, sans dépendre uniquement du fichier slow log. Exemple (MySQL) :

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS nb,
  ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s,
  ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Ce n’est pas un remplacement du slow log (qui donne le SQL exact et des métriques “rows examined”), mais c’est un excellent complément pour repérer les “petites requêtes trop fréquentes”.

Passer du constat à l’action : lire EXPLAIN et concevoir un index utile (pas décoratif)

Quand vous avez votre requête top 3/top 10, l’étape suivante est le plan : EXPLAIN vous dit comment MySQL exécute la requête et quels index (ou scans) il choisit. Sur MySQL 8.0.18+ utilisez EXPLAIN ANALYZE : vous obtenez les temps réels par opérateur (nested loop, range scan, filesort). Sur MariaDB, la syntaxe et la qualité des métriques diffèrent selon version, mais EXPLAIN reste indispensable.

Ce que vous cherchez en priorité :

• type=ALL (full scan) sur une table grosse ;
• Using filesort et/ou Using temporary sur des listings ;
• key nul alors qu’un index devrait matcher ;
• un rows (estimations) délirant, surtout si le temps réel (EXPLAIN ANALYZE) confirme ;
• des conversions implicites (typage) : ex. comparer un INT à une chaîne, ou une colonne collatée différemment dans un JOIN.

Checklist simple pour concevoir un index composite qui sert vraiment :

• placez d’abord les colonnes utilisées en égalité (= / IN) ;
• ensuite les colonnes utilisées en intervalle (>, <, BETWEEN) ;
• puis celles de tri (ORDER BY) si vous voulez éviter un filesort (ça dépend du sens ASC/DESC et du moteur/version) ;
• visez, quand c’est pertinent, un index couvrant (c’est-à-dire que la requête peut être résolue via l’index sans retourner lire la table), mais sans tomber dans “je mets toutes les colonnes” (ça devient un boulet à l’écriture).

La majorité des optimisations “index” en contexte PrestaShop se résument à : créer un index composite aligné sur les filtres les plus sélectifs dans l’ordre d’usage, puis sur l’ORDER BY.

Exemple fréquent en multi-boutique : vous filtrez quasi toujours par id_shop, puis par un statut/date. Si l’index commence par date_add mais que votre WHERE commence par id_shop, vous forcez un travail inutile. Sur les gros back-offices (millions de commandes), une requête de type :

SELECT o.id_order, o.reference, o.date_add
FROM ps_orders o
WHERE o.id_shop = 1 AND o.current_state IN (2,3,4)
ORDER BY o.date_add DESC
LIMIT 50;

peut passer de centaines de ms à quelques dizaines de ms si vous avez un index du type (id_shop, current_state, date_add) et que le plan l’utilise réellement. Ce n’est pas automatique : selon la distribution des états, un autre ordre peut être meilleur. D’où la règle : pas d’index “au feeling”, uniquement à partir d’EXPLAIN/EXPLAIN ANALYZE et de métriques Rows_examined.

Deux anti-patterns très fréquents (et souvent visibles dans le slow log) :

• Fonction sur colonne filtrée : WHERE DATE(o.date_add) = '2026-07-31' empêche l’usage d’un index sur date_add. Préférez une borne : WHERE o.date_add >= '2026-07-31' AND o.date_add < '2026-08-01'.
• Wildcard en tête : LIKE '%robe%' ne peut pas exploiter un index B-Tree classique. Sur un catalogue, ça appelle souvent soit un moteur full-text (si pertinent), soit une stratégie de recherche dédiée, mais ce n’est plus “juste un index”.

Enfin, gardez en tête une réalité PrestaShop : une page lente n’est pas toujours une requête lente. Très souvent, c’est trop de requêtes (hooks, modules, N+1 sur déclinaisons/attributs). Avant de rajouter 10 index, vérifiez si la bonne optimisation n’est pas de réduire le nombre de round-trips SQL.

Déployer (et retirer) des index en production PrestaShop : procédure sûre et cas d’usage typiques

Ajouter un index a un coût : plus d’écriture (INSERT/UPDATE), plus de stockage, et parfois des effets de bord (le plan choisit un index “mauvais” parce que les stats sont bancales). Sur PrestaShop, c’est encore plus vrai dès que vous avez des modules : certains ajoutent des colonnes, des tables, mais oublient systématiquement les index sur les clés de jointure. Un cas réel classique : table custom ps_module_event jointe sur id_order ou id_customer sans index → full scan à chaque affichage BO. La correction est triviale, mais vous devez la déployer proprement.

Procédure minimale recommandée :

1. Identifier les index existants (éviter les doublons) : SHOW INDEX FROM ps_orders;.
2. Valider le gain sur clone/réplique avec EXPLAIN ANALYZE (MySQL) et exécution répétée (important : exécuter plusieurs fois pour amortir le “warmup” du buffer pool).
3. Ajouter l’index en DDL “online” quand possible, sinon via un outil d’OSC (pt-online-schema-change, gh-ost) si table énorme.
4. Mettre à jour les stats : ANALYZE TABLE ...;.
5. Mesurer après déploiement : le but est un gain global (temps total DB, p95/p99), pas une victoire locale.

Exemple (MySQL 8.x / InnoDB, tentative d’online DDL — à tester selon votre moteur/version) :

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

ANALYZE TABLE ps_orders;

Nuance importante : même en LOCK=NONE, vous aurez au minimum des metadata locks très courts au début/à la fin. Sur une boutique à fort trafic, ces quelques secondes peuvent suffire à créer une file d’attente si vous le faites au mauvais moment. Planifiez une fenêtre, et évitez les pics (ex. midi/soir en heure locale, ou pendant une grosse opération commerciale).

Sur MySQL 8, une technique propre pour limiter le risque “le plan change et ça empire” est de tester avec un index invisible (quand c’est compatible avec votre version) : vous créez l’index mais l’optimiseur ne l’utilise pas tant que vous ne le rendez pas visible. Ça permet de mesurer le coût d’écriture/stockage, puis d’activer l’usage ensuite.

Enfin, surveillez après déploiement : le but n’est pas “faire passer une requête de 400 ms à 40 ms” si, en échange, vous ralentissez l’insert de commande et vous dégradez le checkout (surtout si vous avez beaucoup d’updates stock, paniers, sessions, etc.). Concrètement, après ajout d’un index sur une table très écrite, regardez :

• temps moyen/p95 des endpoints de checkout ;
• latence commit InnoDB (si vous monitorez) ;
• évolution de la charge CPU MySQL et du débit disque.

Sur les shops à trafic fort, couplez cette démarche avec une réflexion plus large sur la pagination et les patterns d’accès (OFFSET profond = poison), y compris sur vos endpoints et exports : Index, pagination et pool de connexions pour API (utile aussi pour exports BO).

Et si votre problème est structurel (IO saturées, buffer pool trop petit, mutualisé à genoux), l’optimisation d’index ne compensera pas une infra insuffisante : recadrez l’hébergement (voir par exemple critères techniques d’un hébergement mutualisé fiable et, pour des boutiques volumineuses, serveurs dédiés infogérés (NVMe, Redis, Varnish, HA)).