Indexation MySQL : de recherches de 2 secondes à des résultats en 30 ms

Un index approprié peut transformer une recherche de produits de 2 secondes en 30 ms – une amélioration de 98 %. Pourtant, la plupart des sites WordPress utilisent uniquement les index par défaut, et de nombreuses applications PHP personnalisées sont construites sans aucune stratégie d’indexation. Ce guide vous apprend à concevoir des index qui font rugir votre base de données.

Pourquoi l’indexation est importante pour les performances Web

Lorsqu’un utilisateur visite une page qui interroge une base de données (listes de produits, résultats de recherche, profils utilisateur), MySQL doit trouver les lignes pertinentes. Sans index, MySQL effectue un scan complet de la table – lisant chaque ligne jusqu’à trouver des correspondances. Pour une table de 100 000 lignes, un scan complet prend 200‑500 ms. Avec 1 000 utilisateurs simultanés, cela devient des secondes de retard et le CPU du serveur sature.

Les index sont comme l’index d’un livre : ils indiquent exactement à MySQL où trouver les lignes, transformant une opération O(n) en O(log n). Le résultat est des requêtes qui renvoient des résultats en millisecondes au lieu de secondes.

Comment fonctionnent les index MySQL (en termes simples)

MySQL utilise par défaut des index B‑Tree. Ils stockent les valeurs des colonnes dans une structure arborescente triée. Lorsque vous exécutez une requête avec une clause WHERE sur une colonne indexée, MySQL parcourt l’arbre pour trouver les valeurs correspondantes en environ log₂(N) étapes. Pour 1 million de lignes, cela représente environ 20 étapes au lieu de 1 million de scans.

Règle critique : Les index aident lorsque vous filtrez par égalité (=), plage (<, >, BETWEEN) ou correspondance de préfixe (LIKE 'terme%'). Ils ne peuvent pas aider avec LIKE '%terme%' (wildcard initial) ou les appels de fonction comme WHERE DATE(created_at) = '2025-01-01'.

Identifier les requêtes lentes – utilisation de EXPLAIN

Avant d’ajouter des index, trouvez vos requêtes les plus lentes. Activez le journal des requêtes lentes de MySQL :

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;  -- log queries slower than 0.5 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Exécutez ensuite mysqldumpslow -s t /var/log/mysql/slow.log pour voir les principales requêtes lentes.

Ensuite, préfixez EXPLAIN à toute requête suspecte pour voir comment MySQL l’exécute. Par exemple :

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price < 100;

Regardez la colonne type :

• ALL – scan complet de la table (pire).
• range ou ref – utilisation d’un index (bon).
• const – recherche par clé primaire (meilleur).

Vérifiez également rows – le nombre de lignes scannées. S’il est proche du total de la table, ajoutez un index.

Stratégie pratique d’indexation – index simples vs composites

Index à une colonne

Utilisez-les lorsque vous filtrez fréquemment sur une colonne.

CREATE INDEX idx_user_id ON orders (user_id);

Index composites (multi‑colonnes)

Lorsque vous filtrez sur plusieurs colonnes, un index composite est bien plus efficace que des index séparés. MySQL ne peut utiliser qu’un seul index par référence de table, mais un index composite couvre plusieurs colonnes.

-- Slow: 2 seconds, scans 50,000 rows
SELECT * FROM products WHERE category_id = 5 AND price < 100;

-- Add composite index (order matters!)
CREATE INDEX idx_category_price ON products (category_id, price);

-- Now: 30ms, scans 127 rows

Règle d’ordre : Placez la colonne la plus sélective en premier (celle qui filtre le plus de lignes). Habituellement, category_id réduit 50 000 → 5 000, puis price réduit à 200. Si vous inversez (price, category_id), l’index serait moins efficace pour les requêtes de plage sur le prix.

Index couvrants (le Saint Graal)

Si votre requête n’a besoin que des colonnes qui sont dans l’index, MySQL peut répondre à la requête entièrement à partir de l’index – sans avoir à toucher aux données de la table. C’est extrêmement rapide.

-- Query only needs id and name
SELECT id, name FROM products WHERE category_id = 5;

-- Covering index
CREATE INDEX idx_category_id_name ON products (category_id, name);

-- EXPLAIN will show "Using index" in Extra column

Exemple réel : filtrage de produits e-commerce – avant et après

Une boutique en ligne avec 50 000 produits avait une page de recherche filtrant par catégorie, marque et plage de prix. La requête originale :

SELECT * FROM products 
WHERE category_id = 12 
  AND brand_id IN (3,7,9) 
  AND price BETWEEN 50 AND 200 
ORDER BY price LIMIT 24;

Sans index : scan complet de la table – 2,3 secondes, 50 000 lignes scannées.

Après analyse : Ils ont ajouté un index composite :

CREATE INDEX idx_category_brand_price ON products (category_id, brand_id, price);

Résultat : La même requête s’est exécutée en 80 ms (amélioration de 96 %), ne scannant que 312 lignes. Le temps de chargement de la page est passé de 3,2 secondes à 0,6 seconde.

Indexation pour WordPress (si vous devez rester)

WordPress est tristement célèbre pour ses requêtes lentes en raison de son schéma générique. La table wp_postmeta stocke chaque champ personnalisé sous forme de paire clé‑valeur, ce qui génère des millions de lignes. Vous pouvez ajouter des index personnalisés pour améliorer les requêtes courantes :

-- If you frequently query postmeta by meta_key and meta_value
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(100));

Mais même avec l’indexation, WordPress souffre toujours de la surcharge de wp_query. Les applications PHP personnalisées conçues avec un schéma et des index appropriés seront toujours plus rapides.

Maintenance des index – quand ajouter, quand supprimer

• Ajoutez des index sur les colonnes utilisées dans WHERE, JOIN, ORDER BY et GROUP BY.
• Supprimez les index sur les colonnes rarement utilisées ou ayant une très faible cardinalité (ex. une colonne `status` avec seulement deux valeurs). Chaque index ralentit les opérations INSERT, UPDATE et DELETE car MySQL doit également mettre à jour l’index.
• Surveillez l’utilisation des index avec SHOW INDEX FROM table; et SELECT * FROM sys.schema_unused_indexes; (MySQL 8.0).

Outils d’analyse d’indexation

• EXPLAIN – intégré, commencez toujours ici.
• MySQL Workbench – EXPLAIN visuel et tableau de bord des performances.
• pt‑query‑digest (Percona Toolkit) – analyse le journal des requêtes lentes et suggère des index.
• phpMyAdmin – fonction “Profiling” pour voir les détails d’exécution des requêtes.

Étude de cas client : plateforme immobilière

Un site Web immobilier comptait 200 000 annonces. Les utilisateurs recherchaient par ville, fourchette de prix, chambres et type de bien. La page de recherche mettait 4 à 6 secondes à se charger car la requête scannait toute la table à chaque fois.

Solution : Après avoir analysé les journaux lents et utilisé EXPLAIN, nous avons ajouté un index composite :

CREATE INDEX idx_city_price_beds_type ON properties (city_id, price, bedrooms, property_type);

Nous avons également modifié la requête pour utiliser un index couvrant en sélectionnant uniquement les colonnes nécessaires (id, titre, prix, vignette) au lieu de `SELECT *`.

Résultats :

• Temps de requête : 5,2 secondes → 90 ms.
• La charge CPU du serveur a chuté de 70 %.
• Temps de chargement de la page : 6,5 s → 1,2 s (frontend inclus).
• L’engagement des utilisateurs a augmenté de 34 % car les utilisateurs n’abandonnaient pas les recherches lentes.

Erreurs courantes et comment les corriger

Erreur 1 : Indexer chaque colonne

Problème : Trop d’index ralentissent les écritures. Correction : N’indexez que les colonnes qui apparaissent dans WHERE, JOIN ou ORDER BY.

Erreur 2 : Utiliser des fonctions sur des colonnes indexées

Mauvais : WHERE DATE(created_at) = '2025-01-01' – ignore l’index sur created_at. Bon : WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59'.

Erreur 3 : Wildcard initial dans LIKE

Mauvais : WHERE name LIKE '%widget%' – n’utilise jamais l’index. Bon : Utilisez un index de recherche plein texte pour les correspondances partielles.

Erreur 4 : Ne pas utiliser EXPLAIN avant et après

Exécutez toujours EXPLAIN avant d’ajouter un index, et à nouveau après, pour vérifier l’amélioration.

Avancé : Automatisation des suggestions d’index

Pour MySQL 8.0, vous pouvez activer le performance schema et utiliser le schéma sys pour trouver les index manquants :

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Pour les versions plus anciennes, pt-index-usage de percona-toolkit analyse les journaux lents et suggère des index.

Prêt à optimiser votre base de données ?

Je construis des applications PHP personnalisées avec une indexation appropriée dès le premier jour. Que vous ayez un site WordPress lent ou une application personnalisée avec des goulots d’étranglement au niveau des requêtes, je peux analyser vos journaux lents, ajouter les bons index et souvent réduire les temps de chargement des pages de 50 % ou plus.

Parlons de vos problèmes de performances de base de données. Je fournirai une évaluation gratuite de vos requêtes les plus lentes.

Tous les chiffres de performance proviennent d’audits clients réels utilisant MySQL 8.0 sur VPS Hostinger. Vos résultats peuvent varier en fonction de la taille des tables et du matériel serveur.