Docs SQLite exécutables : EXPLAIN QUERY PLAN

Q: Quelle est la différence entre SCAN et SEARCH dans la sortie ?

SCAN signifie que SQLite lit toutes les lignes d'une table ou d'un index — acceptable sur une petite table, coûteux sur une grosse. SEARCH, à l'inverse, indique un accès direct aux lignes pertinentes via un index ou la clé primaire. Sur une table volumineuse, vous voulez presque toujours voir SEARCH sur les colonnes de filtrage.

Q: Comment vérifier que ma requête utilise bien un index ?

Lancez EXPLAIN QUERY PLAN sur la requête et cherchez USING INDEX ou USING COVERING INDEX dans la sortie. Si vous ne voyez qu'un SCAN sans aucune mention d'index, c'est que SQLite parcourt toute la table — un index bien placé devrait améliorer les choses.

Q: EXPLAIN ou EXPLAIN QUERY PLAN : lequel choisir ?

EXPLAIN renvoie le bytecode bas niveau de la machine virtuelle de SQLite — utile pour comprendre les entrailles du moteur, rarement pour optimiser une requête. EXPLAIN QUERY PLAN, lui, donne un résumé lisible des accès aux tables et de l'usage des index. Pour le tuning, c'est presque toujours EXPLAIN QUERY PLAN qu'il vous faut.

EXPLAIN QUERY PLAN : visualiser le plan d'exécution d'une requête SQLite

Avant d'optimiser une requête SQLite lente, il faut comprendre ce que SQLite fait réellement. EXPLAIN QUERY PLAN affiche un résumé concis de la stratégie choisie par le planificateur : quelles tables sont consultées, dans quel ordre, et quels index sont utilisés (le cas échéant). La requête n'est pas exécutée pour autant — vous obtenez uniquement le plan d'exécution.

Il suffit de placer ces mots-clés devant n'importe quelle instruction :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

Le résultat ressemble à peu près à ceci :

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

Cette seule ligne en dit long : SQLite effectue un SEARCH (et non un scan) sur la table users, en s'appuyant sur l'index unique créé automatiquement pour email, avec email comme clé de recherche. Exactement ce qu'on espérait.

SCAN vs SEARCH dans SQLite : la première chose à lire

Chaque ligne du plan d'exécution commence par SCAN ou par SEARCH. Cette distinction est le signal le plus important de toute la sortie.

SCAN <table> — SQLite parcourt chaque ligne de la table (ou chaque entrée d'un index). Le coût augmente avec la taille de la table.
SEARCH <table> USING ... — SQLite saute directement aux lignes correspondantes via un index ou une clé primaire. Le coût dépend de la taille du résultat, pas de celle de la table.

Voici une comparaison côte à côte. Une colonne possède un index, l'autre non :

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

Le premier plan affiche SEARCH orders USING INDEX idx_orders_customer. Le second, lui, indique SCAN orders — aucun index sur status, donc SQLite parcourt toutes les lignes. Sur une petite table, ça passe inaperçu ; sur une table d'un million de lignes, on passe de quelques millisecondes à plusieurs secondes.

Un SCAN n'est pas systématiquement un problème. Pour de petites tables de référence, ou pour des requêtes qui ramènent réellement la majorité des lignes, le scan est le bon choix. En revanche, sur une grosse table avec un filtre sélectif, voir SCAN doit vous mettre la puce à l'oreille : il est temps d'ajouter un index.

Vérifier qu'un index est bien utilisé dans une requête SQLite

L'expression à repérer dans le plan d'exécution est USING INDEX <name> (ou USING COVERING INDEX <name> — on y revient plus bas). Si vous avez créé un index en espérant que le planificateur le choisisse, voici comment vous en assurer :

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

Vous devriez voir SEARCH events USING INDEX idx_events_user (user_id=?). Si à la place le plan affiche SCAN events, c'est que quelque chose empêche le planificateur d'utiliser l'index. Les causes les plus fréquentes : envelopper la colonne dans une fonction (WHERE lower(user_id) = ...), comparer des types différents, ou utiliser LIKE '%foo%' avec un caractère générique en début de motif.

Un petit test rapide pour s'en convaincre :

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

Ce + 0 neutralise l'index — le plan retombe sur un SCAN events. N'importe quelle expression appliquée à la colonne indexée produit le même effet.

Les index couvrants apparaissent différemment

Quand un index contient toutes les colonnes dont la requête a besoin, SQLite peut répondre directement depuis l'index, sans jamais toucher la table. Le plan affiche alors USING COVERING INDEX :

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

Le plan : SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). La requête demande price, et l'index contient déjà sku et price — du coup, SQLite ne touche jamais à la table de base. Un index couvrant (covering index), c'est le plan le plus rapide possible pour une recherche : à garder en tête au moment de choisir quelles colonnes regrouper dans un même index.

Lire le plan d'exécution d'une jointure SQLite

C'est avec les jointures que les plans deviennent vraiment intéressants. Chaque ligne du plan correspond à une table de la jointure, et l'ordre des lignes reflète l'ordre dans lequel SQLite parcourt ces tables. La première table est la table externe ; les suivantes sont consultées une fois pour chaque ligne de cette table externe.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

Un plan classique :

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

Lecture de haut en bas : SQLite repère le client unique via sa clé primaire, puis pour ce client va chercher les commandes correspondantes grâce à l'index sur customer_id. Les deux lignes affichent SEARCH — aucun parcours complet — c'est exactement ce qu'on veut.

Si la seconde ligne affichait SCAN o, chaque recherche de client déclencherait un parcours intégral de la table orders. Sur une grosse table, c'est la catastrophe assurée. Le remède, dans la quasi-totalité des cas : ajouter un index sur la colonne de jointure.

Requêtes composées et sous-requêtes

Les plans d'exécution pour UNION, EXCEPT et les sous-requêtes s'imbriquent. Chaque branche apparaît indentée sous son parent :

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

Sous l'en-tête COMPOUND QUERY, vous verrez deux lignes enfants, une par branche. Les sous-requêtes et les CTE fonctionnent sur le même principe : chacune obtient son propre nœud de plan indenté, et vous le lisez avec la même grille de lecture SCAN vs SEARCH.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

La sous-requête apparaît comme un nœud distinct dans le plan (« LIST SUBQUERY » ou équivalent), avec sa propre stratégie d'accès. Les mêmes vérifications s'appliquent à chaque niveau.

EXPLAIN vs EXPLAIN QUERY PLAN

Ce sont deux choses bien différentes, et on les confond souvent.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN tout court (sans QUERY PLAN) crache le bytecode que la machine virtuelle de SQLite va exécuter — des dizaines d'opcodes bas niveau du genre OpenRead, SeekRowid, Column, ResultRow. Pratique si vous déboguez le moteur lui-même. Quasiment jamais utile pour optimiser une requête SQLite.

EXPLAIN QUERY PLAN, c'est le résumé lisible que vous voulez vraiment. Dans le doute, partez toujours sur EXPLAIN QUERY PLAN.

Méthode pour traiter une requête SQL lente sous SQLite

Quand une requête traîne, la boucle est toujours la même :

Lancez EXPLAIN QUERY PLAN dessus.
Pour chaque ligne correspondant à une table, posez-vous la question : est-ce un SCAN ou un SEARCH ? Sur une grosse table, c'est le SCAN qui est suspect.
Si un SCAN filtre sur une colonne, envisagez d'ajouter un index sur cette colonne.
Pour les jointures, vérifiez que les tables de la boucle interne utilisent bien SEARCH USING INDEX sur la colonne de jointure.
Relancez EXPLAIN QUERY PLAN après avoir créé l'index. Le plan d'exécution doit changer. S'il ne bouge pas, c'est que le planificateur a jugé votre index inutile — en général parce que la table est petite ou que le filtre n'est pas assez sélectif.

Un exemple concret de l'étape 5 :

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

Le plan est passé de SCAN à SEARCH. C'est le signe que l'index fait bien son travail. (Sur une table fraîche et quasi vide, le planificateur peut continuer à faire un scan parce qu'il n'y a pas assez de données pour que l'index vaille le coup — remplissez la table ou lancez ANALYZE, et le choix bascule souvent.)

Ce que le plan d'exécution ne vous dira pas

EXPLAIN QUERY PLAN décrit la stratégie, pas le coût. Il ne vous dira pas que la requête a pris 800 ms ou renvoyé 50 000 lignes. Pour ça, il vous faut le chronométrage (.timer on dans la CLI) et le nombre de lignes. Le plan et le timing sont complémentaires : le plan vous dit pourquoi une requête est lente, le chrono vous dit si elle l'est vraiment.

Deux autres limites à garder en tête :

Le plan peut évoluer à mesure que les données grossissent. Une requête qui scannait tranquillement une table de 100 lignes aura besoin d'un index quand la table atteindra le million. Revérifiez les plans sur des données de taille réelle (production), pas sur vos fixtures de dev.
Le planificateur s'appuie sur les statistiques collectées par ANALYZE. Sans elles, il retombe sur des valeurs par défaut pas toujours pertinentes. Des stats absentes ou périmées sont une cause fréquente de plans surprenants.

La suite : ANALYZE et VACUUM

Le planificateur de requêtes prend ses décisions à partir des statistiques sur vos tables et vos index. Si ces stats sont absentes ou obsolètes, même un schéma parfaitement indexé peut produire un mauvais plan. ANALYZE sert justement à les garder à jour — et VACUUM est la commande compagnon pour récupérer de l'espace et défragmenter le fichier de base de données. C'est ce qu'on voit ensuite.

Questions fréquentes

À quoi sert EXPLAIN QUERY PLAN dans SQLite ?

Cette commande demande à SQLite de décrire comment il exécuterait votre requête, sans la lancer pour de vrai. Le plan affiché indique quelles tables sont parcourues, quels index sont utilisés et dans quel ordre les jointures sont effectuées. Il suffit de préfixer un SELECT, INSERT, UPDATE ou DELETE par EXPLAIN QUERY PLAN pour récupérer ce plan.

Quelle est la différence entre SCAN et SEARCH dans la sortie ?

SCAN signifie que SQLite lit toutes les lignes d'une table ou d'un index — acceptable sur une petite table, coûteux sur une grosse. SEARCH, à l'inverse, indique un accès direct aux lignes pertinentes via un index ou la clé primaire. Sur une table volumineuse, vous voulez presque toujours voir SEARCH sur les colonnes de filtrage.

Comment vérifier que ma requête utilise bien un index ?

Lancez EXPLAIN QUERY PLAN sur la requête et cherchez USING INDEX <nom> ou USING COVERING INDEX <nom> dans la sortie. Si vous ne voyez qu'un SCAN <table> sans aucune mention d'index, c'est que SQLite parcourt toute la table — un index bien placé devrait améliorer les choses.

EXPLAIN ou EXPLAIN QUERY PLAN : lequel choisir ?

EXPLAIN renvoie le bytecode bas niveau de la machine virtuelle de SQLite — utile pour comprendre les entrailles du moteur, rarement pour optimiser une requête. EXPLAIN QUERY PLAN, lui, donne un résumé lisible des accès aux tables et de l'usage des index. Pour le tuning, c'est presque toujours EXPLAIN QUERY PLAN qu'il vous faut.

Concepts liés

SQLite EXPLAIN QUERY PLAN : analyser une requête lente