Une requête lente n'est presque jamais lente parce qu'elle calcule trop. Elle est lente parce qu'elle lit trop : des lignes dont elle n'a pas besoin, en quantité, et souvent plusieurs fois.
Tous les chiffres de cet article viennent d'une même base de démonstration — 100 000 commandes, 45 000 clients, 250 000 lignes de commande — interrogée avec SQLite 3.54 compilé en WebAssembly. Les durées sont des moyennes sur plusieurs exécutions. Sur PostgreSQL ou MySQL, les mots du plan changent ; la mécanique, non.
Lire le plan avant de toucher à quoi que ce soit
Les commandes d'un client :
SELECT COUNT(*), MIN(passee_le)
FROM commande
WHERE client_id = 1234;Huit commandes, 7,9 ms. Rien d'alarmant. Mais demande au moteur comment il s'y prend :
EXPLAIN QUERY PLAN
SELECT COUNT(*), MIN(passee_le) FROM commande WHERE client_id = 1234;
-- SCAN commandeSCAN commande : il lit la table entière, cent mille lignes, pour en garder
huit. Le résultat est juste, le chemin est absurde, et il coûtera dix fois plus
le jour où la table sera dix fois plus grosse.
Le plan est le premier réflexe, avant toute réécriture. Chaque moteur a le sien :
| Moteur | Commande | Ce qu'on y cherche |
|---|---|---|
| SQLite | EXPLAIN QUERY PLAN |
SCAN (tout lire) ou SEARCH … USING INDEX |
| PostgreSQL | EXPLAIN ANALYZE |
Seq Scan, ou Index Scan et Index Only Scan |
| MySQL | EXPLAIN ANALYZE |
Table scan, ou Index lookup |
Une précaution : EXPLAIN ANALYZE exécute vraiment la requête pour la
chronométrer. Sur un UPDATE ou un DELETE, enveloppe-le dans une transaction
que tu annules ensuite.
Un index, c'est un second exemplaire, trié
Un index est une copie d'une ou plusieurs colonnes, triée, où chaque valeur garde l'adresse de sa ligne. Le tri change tout : dans une liste triée, on ne cherche pas, on descend.
SQLite et PostgreSQL rangent cette copie dans un B-tree, un arbre de pages de taille fixe — 4 Ko par défaut chez SQLite, 8 Ko chez PostgreSQL. La racine dit dans quelle page descendre, chaque niveau resserre l'intervalle, et les feuilles portent les valeurs.
CREATE INDEX ix_commande_client ON commande (client_id);
-- SEARCH commande USING INDEX ix_commande_client (client_id=?)Cet index tient en 265 pages : une racine et 264 feuilles. Trouver le client 1234, c'est lire la racine, une feuille, puis les huit lignes visées. La même requête passe de 7,9 ms à 0,015 ms, cinq cents fois moins, sans qu'on ait changé un caractère.
L'écart se creuse avec la taille. Un parcours complet coûte deux fois plus quand la table double ; un B-tree ne gagne un niveau que lorsqu'elle est multipliée par quelques centaines.
Quand l'absence d'index se multiplie
Huit millisecondes ne font peur à personne. Le problème commence quand la requête les répète. La date de la dernière commande des 2 000 premiers clients :
SELECT c.id,
(SELECT MAX(o.passee_le)
FROM commande o
WHERE o.client_id = c.id) AS derniere
FROM client c
WHERE c.id <= 2000;SEARCH c USING INTEGER PRIMARY KEY (rowid<?)
CORRELATED SCALAR SUBQUERY 1
SEARCH oCORRELATED : la sous-requête dépend de la ligne courante, elle est rejouée
pour chacun des 2 000 clients. Et ce SEARCH o, sans USING INDEX, n'a rien
d'une recherche : faute d'index, chaque passage relit la table entière. Deux
mille passages de cent mille lignes, deux cents millions de lectures :
13,9 secondes.
Avec l'index sur client_id, la même requête répond en 11,7 ms. Mille fois
moins. Un index manquant ne coûte pas une constante, il multiplie : par le
nombre de lignes lues à chaque passage, puis par le nombre de passages.
L'ordre des colonnes décide de tout
Un index peut porter plusieurs colonnes. (client_id, passee_le) trie d'abord
par client, puis par date à l'intérieur de chaque client — comme un annuaire
trié par nom, puis par prénom.
Il sert donc tout ce qui commence par le client :
CREATE INDEX ix_client_date ON commande (client_id, passee_le);
SELECT COUNT(*) FROM commande
WHERE client_id = 1234 AND passee_le >= '2024-01-01';
-- SEARCH commande USING COVERING INDEX ix_client_date (client_id=? AND passee_le>?)Mais pas une condition sur la date seule :
SELECT COUNT(*) FROM commande WHERE passee_le >= '2025-06-01';
-- SCAN commande USING COVERING INDEX ix_client_dateSCAN, encore. Les commandes de juin 2025 sont éparpillées dans les 45 000
groupes de clients : aucune plage continue ne les contient, l'index se lit en
entier. 4,4 ms, contre 0,017 ms pour la requête qui commence par le client.
La règle tient en une phrase : les colonnes testées par égalité d'abord, la colonne de plage ensuite, et l'index ne sert plus si sa première colonne manque à la requête. Un annuaire trié par nom ne t'aide pas à trouver tous les « Camille ».
Les moteurs savent parfois contourner la règle — SQLite saute une première
colonne qui a très peu de valeurs distinctes quand ANALYZE le lui a appris,
et PostgreSQL 18 a ajouté le même « skip scan » —, mais c'est une optimisation
d'opportunité, pas une stratégie.
L'index couvrant : ne plus toucher à la table
Dans le plan précédent, un mot de plus : COVERING. L'index contient toutes les
colonnes dont la requête a besoin, et la table n'est même pas ouverte.
SELECT passee_le FROM commande WHERE client_id = 1234;
-- SEARCH commande USING COVERING INDEX ix_client_date (client_id=?)
SELECT passee_le, statut FROM commande WHERE client_id = 1234;
-- SEARCH commande USING INDEX ix_client_date (client_id=?)Demander statut en plus oblige à aller chercher chaque ligne dans la table.
PostgreSQL appelle le premier cas un Index Only Scan, et permet d'embarquer
des colonnes dans un index sans les trier, avec INCLUDE.
La sous-requête corrélée de tout à l'heure en profite doublement : avec
(client_id, passee_le), le MAX(passee_le) d'un client est simplement la
dernière entrée de sa plage. Elle tombe à 1,1 ms — plus de dix mille fois
moins que sans index.
Ce qui empêche un index de servir
L'index existe, et le plan dit SCAN quand même. Le plus souvent, la requête a
rendu la colonne méconnaissable.
-- Une fonction sur la colonne : il faut la calculer ligne par ligne.
SELECT COUNT(*) FROM commande WHERE strftime('%Y', passee_le) = '2024';
-- SCAN commande USING COVERING INDEX ix_commande_date 24 ms
-- La même question, posée comme une plage : l'index la découpe directement.
SELECT COUNT(*) FROM commande
WHERE passee_le >= '2024-01-01' AND passee_le < '2025-01-01';
-- SEARCH commande USING COVERING INDEX ix_commande_date (passee_le>? AND passee_le<?) 1,0 ms| Écriture | L'index sert ? | Pourquoi |
|---|---|---|
strftime('%Y', d) = '2024' |
Non | une fonction appliquée à la colonne |
d >= '2024-01-01' AND d < '2025-01-01' |
Oui | une plage sur la valeur brute |
nom LIKE '%café' |
Non | le début du motif est inconnu |
nom LIKE 'café%' |
Selon le moteur | un préfixe se lit comme une plage, si la collation s'y prête |
code = 1234 sur une colonne texte |
Selon le moteur | MySQL convertit alors chaque ligne, pas la valeur cherchée |
Quand la fonction est vraiment la question — tu filtres toujours par année —,
indexe l'expression elle-même. SQLite accepte
CREATE INDEX ix_annee ON commande (strftime('%Y', passee_le)), et son plan
devient SEARCH … USING COVERING INDEX ix_annee (<expr>=?). PostgreSQL fait de
même avec extract(year from passee_le), sur une colonne timestamp : pas sur
un timestamptz, dont l'année dépend du fuseau.
Le planificateur choisit, avec ce qu'il sait
Un index ne sert pas parce qu'il existe. Entre la requête et les données, le planificateur estime ce que coûte chaque chemin et prend le moins cher. Il ne lit pas les données pour décider : il s'appuie sur des statistiques — combien de lignes, combien de valeurs distinctes par colonne.
Un index sert à trouver peu de lignes parmi beaucoup. Sur la colonne
statut, 97 % des commandes sont « livrées » : pour elles, passer par l'index
oblige à faire un aller-retour entre l'index et la table pour presque chaque
ligne. Mesuré :
| Filtre | Lignes | Par l'index | Table entière |
|---|---|---|---|
statut = 'annulée' |
2 526 | 1,2 ms | 5,9 ms |
statut = 'livrée' |
97 053 | 14,7 ms | 9,5 ms |
SQLite a pourtant choisi l'index dans les deux cas. Après ANALYZE, sa table
sqlite_stat1 ne lui dit qu'une moyenne — 100 000 lignes, quatre valeurs, donc
25 000 lignes par valeur — et rien de la valeur rare ni de la valeur écrasante.
PostgreSQL, qui garde la liste des valeurs les plus fréquentes, aurait lu la
table. D'où deux habitudes : faire tourner ANALYZE (l'autovacuum s'en charge
chez PostgreSQL), et ne jamais croire un plan sans l'avoir chronométré.
Ce que coûte un index
Rien n'est gratuit. Chaque INSERT, UPDATE ou DELETE met à jour tous
les index de la table : vingt mille insertions prennent 52 ms sans index,
102 ms avec trois. Et chaque index pèse — un peu plus d'un mégaoctet pour celui
de client_id, sur une table de cent mille lignes.
On indexe donc les requêtes qu'on exécute, pas les colonnes qu'on a. Une démarche en quatre temps :
- Trouver les requêtes lentes ou fréquentes :
pg_stat_statements, le journal des requêtes lentes de MySQL, ou un simple chronomètre. - Lire leur plan.
- Indexer ce que le plan réclame : les colonnes du
WHEREtestées par égalité, puis celle de la plage ou duORDER BY. - Mesurer à nouveau. Un index que le plan n'utilise pas est un coût sans contrepartie : on le supprime.
En résumé
- Une requête lente lit trop : le plan le dit avant le chronomètre.
SCANlit tout ;SEARCH … USING INDEXdescend dans un B-tree trié.- Dans une sous-requête corrélée, un index manquant multiplie le coût : 13,9 s sans, 11,7 ms avec.
- Dans un index composite : l'égalité d'abord, la plage ensuite.
- Un index couvrant évite jusqu'à la table.
- Une fonction sur la colonne désactive l'index : écris une plage, ou indexe l'expression.
- Un index sert à trouver peu de lignes ; au-delà, la table entière va plus vite.
- Chaque index ralentit les écritures : on indexe des requêtes, pas des colonnes.