Index partiels SQLite : CREATE INDEX ... WHERE

Un index partiel ne couvre qu'une partie des lignes

Un index classique contient une entrée pour chaque ligne de la table. Un index partiel, lui, ne référence que les lignes qui satisfont la clause WHERE indiquée à sa création. Résultat : un index plus compact, moins de pages à parcourir et moins de travail à chaque INSERT ou UPDATE qui ne concerne pas la portion indexée.

La syntaxe reste un CREATE INDEX tout ce qu'il y a de plus classique, avec un WHERE ajouté à la fin :

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending ne contient que les lignes où status = 'pending'. Les commandes expédiées, annulées ou remboursées en sont totalement absentes. Si 95 % de votre table orders correspond à de l'historique et que vos requêtes ciblent surtout les commandes ouvertes, on parle d'un index 20× plus petit pour la même vitesse de requête.

Quand le planificateur va réellement l'utiliser

Un index partiel n'est exploitable que si SQLite peut prouver que votre requête se limite aux mêmes lignes que celles couvertes par l'index. Le plus simple : reprendre la clause WHERE de l'index dans votre requête :

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Le plan doit mentionner USING INDEX idx_orders_pending. Si vous retirez status = 'pending' de la requête, le planificateur retombe sur un parcours complet de la table — il n'a aucun moyen de savoir que la requête reste cantonnée au sous-ensemble indexé.

La règle à retenir : le WHERE de la requête doit impliquer le WHERE de l'index. Une égalité sur la même colonne avec la même valeur, c'est le cas évident et sans risque. Les inégalités et les OR, c'est plus délicat ; vérifiez toujours avec EXPLAIN QUERY PLAN.

Pourquoi s'embêter — les trois gains

Trois raisons concrètes qui justifient l'usage d'un index partiel :

1. Moins d'espace disque. Seules les lignes qui matchent sont stockées. Sur un scénario où « 1 % de la table est chaude », l'index pèse environ 1 % d'un index complet.
2. Écritures moins coûteuses. Les INSERT et UPDATE ne touchent à l'index que si la ligne satisfait le filtre. Un INSERT avec status = 'shipped' sur la table ci-dessus ne touche pas du tout à idx_orders_pending.
3. Vitesse de lecture identique. Une recherche dans un B-tree est logarithmique par rapport à la taille de l'index. Plus l'index est petit, plus la recherche est (légèrement) rapide, mais le vrai bénéfice est ailleurs : moins de cache miss, moins d'I/O.

Si une colonne est très déséquilibrée — la majorité des lignes ont la même valeur et vous ne vous intéressez qu'aux quelques exceptions — c'est le cas d'école pour un index partiel.

Index uniques partiels (la fonctionnalité qui change tout)

Une contrainte UNIQUE classique s'applique à toutes les lignes. Et ça devient un vrai problème dès qu'on introduit le soft delete :

-- Échoue : il y a deux lignes avec email = 'a@x.com', même si l'une est supprimée.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Un index partiel unique vous permet de garantir l'unicité uniquement sur les lignes qui comptent vraiment :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- supprimé de manière logique
    ('a@x.com', '2026-01-05'),  -- de nouveau supprimé de manière logique
    ('a@x.com', NULL);          -- une seule ligne active, autorisée

SELECT email, deleted_at FROM users;

Trois lignes, le même e-mail, et aucune violation de contrainte — parce que seule la ligne où deleted_at IS NULL entre dans la vérification d'unicité. Essayez d'insérer une deuxième ligne active avec le même e-mail : SQLite lèvera aussitôt UNIQUE constraint failed.

Ce schéma revient partout : un seul abonnement actif par client, une seule adresse principale par utilisateur, une seule facture ouverte par commande. L'index partiel unique exprime cette règle de façon directe.

Indexer autour des NULL

Les NULL se comportent bizarrement vis-à-vis des index. Un besoin classique consiste à « ignorer complètement les NULL » : imaginez une colonne external_id peu remplie, où la plupart des lignes sont à NULL, mais où les valeurs renseignées doivent rester uniques.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Bidule',  'EXT-001'),
    ('Gadget',  NULL),
    ('Machin', NULL),
    ('Engrenage', 'EXT-002');

SELECT name, external_id FROM products;

Deux NULL cohabitent sans souci, et les lignes EXT-001 et EXT-002 restent garanties uniques. L'index occupe aussi moins de place — les lignes NULL ne sont tout simplement pas stockées — ce qui rend les recherches par external_id rapides, même quand la table grossit.

Ce que la clause de filtrage peut référencer

La clause WHERE d'un index partiel sqlite est restrictive. Elle peut faire référence à :

• Des colonnes de la table indexée.
• Des constantes littérales.
• Un petit ensemble de fonctions intégrées déterministes.

En revanche, elle ne peut pas référencer :

• D'autres tables.
• Des sous-requêtes.
• Des fonctions non déterministes comme random() ou CURRENT_TIMESTAMP.
• Des paramètres ou des variables.

Et c'est logique : SQLite doit évaluer ce filtre à chaque INSERT et UPDATE, donc le résultat doit rester stable. Du coup, ceci fonctionne :

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

En revanche, WHERE created_at > date('now') ne convient pas : la valeur de date('now') évolue avec le temps, donc l'ensemble des lignes indexées changerait au fil de l'eau, ce que SQLite ne sait pas gérer.

Une méthode de vérification rapide

Avant de valider un index partiel, passez-le au crible de ces trois questions :

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. Le planificateur choisit-il l'index ?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Ignore-t-il les requêtes en dehors du filtre ?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Évite-t-il les requêtes sans filtre du tout ?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

La requête 1 doit utiliser idx_jobs_runnable. Les requêtes 2 et 3 retomberont sur un parcours complet (ou sur un autre index, si vous en avez un). Si le planificateur choisit l'index partiel pour une requête à laquelle vous ne vous attendiez pas, relisez le filtre — il est sans doute plus large que ce que vous pensiez.

Quand éviter l'index partiel

L'index partiel est un outil tranchant. Voici quand il vaut mieux passer son chemin :

• Le filtre couvre la majorité de la table. Si « actif » représente 90 % de vos lignes, votre index partiel n'est qu'un index classique avec des étapes en plus. Indexez simplement la colonne.
• Vos requêtes ne reproduisent pas le filtre tel quel. Si votre code passe par un ORM qui génère WHERE status IN (?, ?, ?), ou qui construit le filtre dynamiquement, le planificateur ne reconnaîtra souvent pas la correspondance. Vérifiez avec EXPLAIN QUERY PLAN, ne supposez rien.
• Le sous-ensemble chaud bouge dans le temps. Un index partiel sur « les commandes des 30 derniers jours » paraît séduisant, mais ce n'est pas exprimable — le filtre doit être déterministe. Il faudrait reconstruire l'index, ou repenser le schéma (une table dédiée recent_orders, ou un booléen archived que vous basculez chaque nuit).

Quand le filtre est stable et ne touche qu'une petite tranche d'une grosse table, l'index partiel fait partie des optimisations au meilleur rapport effort/gain dans SQLite.

Et ensuite : lire les plans d'exécution

Toute cette page s'est appuyée sur EXPLAIN QUERY PLAN pour confirmer qu'un index était bel et bien utilisé. Cet outil mérite sa propre page : comment lire sa sortie, ce que veulent dire les mots-clés, et comment repérer la différence entre une vraie recherche par index et un parcours complet déguisé. C'est ce qu'on verra ensuite.