Pourquoi est-ce lent ? Index et EXPLAIN

Un parcours complet

Placez EXPLAIN QUERY PLAN devant n’importe quelle requête et elle n’est pas exécutée ; c’est le plan qui revient.

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE name = 'Quill Helmet';

SCAN products

SCAN signifie que la base a lu chaque ligne de la table et testé chacune d’elles. products.name n’a pas d’index, il n’y a donc rien de mieux à disposition. Sur 400 lignes, personne ne le remarque. La raison de s’en soucier, c’est que ce coût croît proportionnellement à la table : le même plan sur dix millions de lignes en lit dix millions, à chaque exécution.

Une recherche par index

price, lui, a un index.

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price > 2000;

SEARCH products USING INDEX idx_products_price (price>?)

SEARCH est le mot que vous voulez voir. La base est allée dans l’index, a sauté directement là où commencent les prix au-dessus de 2000, et n’a lu qu’à partir de là — aucune ligne hors de la réponse n’a été touchée. Les autres moteurs utilisent d’autres mots pour la même distinction : PostgreSQL dit Seq Scan contre Index Scan, le EXPLAIN de MySQL affiche type: ALL contre type: range, et SQL Server dessine un Table Scan contre un Index Seek.

Une jointure

EXPLAIN QUERY PLAN
SELECT o.order_number, c.last_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'delivered';

SEARCH o USING INDEX idx_orders_status (status=?)
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)

Lisez cela comme une boucle : trouver les commandes livrées via l’index sur le statut, puis pour chacune retrouver son client par clé primaire. Les deux étapes sont des recherches, et c’est à quoi ressemble une jointure saine. Le plan à surveiller est un SCAN du côté intérieur — c’est une lecture complète de table par ligne de l’extérieur, et la raison habituelle pour laquelle une jointure rapide en développement prend des minutes en production. Les colonnes de clé étrangère veulent un index ; la plupart des bases, SQLite et MySQL comprises, n’en créent pas pour vous.

Comment perdre un index

orders.ordered_at est indexée. Cette requête filtre dessus et lit pourtant toute la table.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE substr(ordered_at, 1, 4) = '2025';

SCAN orders

L’index stocke ordered_at, pas substr(ordered_at, 1, 4). Dès que la colonne est enveloppée dans une fonction, l’ordre stocké ne dit plus rien à la base, qui doit calculer l’expression pour chaque ligne afin de savoir. Il en va de même pour WHERE YEAR(created_at) = 2025, WHERE UPPER(email) = ... et WHERE price * 1.1 > 100.

Réécrivez-la pour que la colonne nue fasse face à la comparaison :

EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE ordered_at >= '2025-01-01'
  AND ordered_at <  '2026-01-01';

SEARCH orders USING INDEX idx_orders_ordered_at (ordered_at>? AND ordered_at<?)

Les mêmes lignes, et cette fois l’index travaille. Règle générale : laissez la colonne indexée seule d’un côté de la comparaison et déplacez le calcul de l’autre. Transformer une fonction sur une date en intervalle semi-ouvert en est de loin l’application la plus fréquente.

ORDER BY peut être gratuit

EXPLAIN QUERY PLAN
SELECT * FROM products ORDER BY price DESC LIMIT 20;

SCAN products USING INDEX idx_products_price

Une troisième forme : pas une recherche, mais pas non plus un parcours désordonné. La requête veut les lignes par prix, et l’index est déjà trié par prix : la base parcourt l’index et s’arrête après 20 lignes — aucun tri. Sans cet index, elle lirait les 400 lignes, les trierait et en jetterait 380.

Voilà pourquoi un index aide autant ORDER BY ... LIMIT que WHERE, et pourquoi les requêtes « top 20 par X » méritent un coup d’œil : avec le bon index elles touchent 20 lignes, sans lui toute la table.

Une courte liste de contrôle

• Indexez les colonnes sur lesquelles vous filtrez et joignez — les clés étrangères surtout, qui en obtiennent rarement un automatiquement.
• Gardez les colonnes indexées hors des appels de fonctions dans WHERE.
• Préférez LIKE 'abc%' à LIKE '%abc%' quand c’est possible ; un joker en tête ne peut pas utiliser d’index.
• Les index ne sont pas gratuits — chacun doit être mis à jour à chaque écriture : indexez ce que vous interrogez vraiment, pas toutes les colonnes.
• Mesurez, ne devinez pas. Exécutez EXPLAIN avant et après la modification, et comparez les deux plans.

Où aller ensuite

C’est la fin du parcours de base. Les pages par dialecte mettent trois tâches — la pagination, l’upsert, et le traitement des chaînes et des dates — côte à côte pour MySQL, PostgreSQL, SQL Server, Oracle et SQLite, parce que ce sont les trois endroits où la syntaxe diffère assez pour qu’une requête qui marche doive être réécrite avant de tourner sur un autre moteur.