Index SQLite : CREATE INDEX et quand les utiliser

Ce qu'est réellement un index

Un index est une structure de données distincte — un arbre B trié — qui permet à SQLite de retrouver des lignes par la valeur d'une colonne sans parcourir toute la table. Sans index, une requête comme WHERE email = 'rosa@example.com' lit chaque ligne et vérifie une par une. Avec un index sur email, SQLite descend dans l'arbre en environ log(n) étapes et tombe directement sur la bonne ligne.

Cette accélération a un prix. L'index est en fait une copie de la colonne indexée plus un pointeur vers la ligne d'origine. Du coup, chaque INSERT, chaque UPDATE sur une colonne indexée et chaque DELETE doit aussi mettre à jour l'index. L'espace disque augmente, et le débit en écriture baisse légèrement. Le compromis est clair : on paie un peu à l'écriture pour gagner beaucoup à la lecture.

Créer un index SQLite

Voici la syntaxe de base :

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

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

Convention de nommage : la plupart des équipes utilisent idx_<table>_<colonne>, comme ça on voit tout de suite à quoi sert l'index. Attention, le nom doit être unique dans toute la base, pas seulement dans la table — d'où la présence du nom de la table dans le nom de l'index.

Pour en supprimer un :

DROP INDEX idx_users_email;

Les index sont une simple béquille de performance. En supprimer un n'altère jamais vos données — seule la vitesse des requêtes est en jeu.

Index uniques en SQLite

Un index unique fait coup double : il accélère les recherches et garantit qu'aucune ligne ne partage la même valeur indexée.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

Le troisième INSERT échoue avec le message UNIQUE constraint failed: accounts.username. SQLite crée automatiquement un index unique pour les colonnes PRIMARY KEY et UNIQUE — vous les verrez sous la forme sqlite_autoindex_<table>_<n>. Vous n'avez donc besoin d'écrire CREATE UNIQUE INDEX que lorsque la contrainte n'a pas été déclarée directement sur la table.

Ce que fait vraiment le planificateur de requêtes

Ajouter un index ne garantit pas que SQLite va s'en servir. Le planificateur choisit une stratégie pour chaque requête, et vous pouvez voir laquelle il a retenue avec EXPLAIN QUERY PLAN :

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

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

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

Cherchez SEARCH ... USING INDEX idx_orders_customer dans la sortie — c'est le signe que l'index est bel et bien utilisé. Si vous tombez sur SCAN orders, c'est que le planificateur a jugé qu'un parcours complet de la table revenait moins cher (ce qui est souvent vrai sur les petites tables), ou alors la forme de votre requête l'empêchait d'exploiter l'index. Un document entier est consacré à la lecture de ces plans, on y revient plus loin.

Les cas où l'index ne sert à rien

Les index ont quelques angles morts bien connus. Chacun des cas suivants neutralise l'index sur email :

-- La fonction enveloppe la colonne
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Caractère générique en début dans LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- L'incompatibilité de type force une conversion
SELECT * FROM users WHERE email = 12345;

Le B-tree est trié sur la valeur brute de email. Du coup, dès qu'on transforme la colonne au moment de la requête, on force un scan complet. Plusieurs solutions : stocker la donnée déjà normalisée (une colonne email_lower), utiliser un index d'expression (CREATE INDEX idx ON users(lower(email))), ou s'appuyer sur la recherche plein texte de SQLite pour les correspondances partielles.

Index couvrant SQLite

Quand un index contient toutes les colonnes dont la requête a besoin, SQLite peut répondre sans jamais lire la table — c'est ce qu'on appelle un index couvrant. L'astuce consiste à ajouter des colonnes supplémentaires dans la définition de l'index :

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

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

Comme les deux colonnes demandées par la requête se trouvent déjà dans l'index, SQLite affiche USING COVERING INDEX. Aucun aller-retour vers la table n'est nécessaire. Les index couvrants font partie des optimisations les plus rentables sur les chemins de lecture critiques — la contrepartie, c'est un index plus volumineux. Les index multi-colonnes méritent un chapitre à part entière : le prochain document s'y attarde comme il faut.

Lister et inspecter les index SQLite

Deux façons de voir ce qui est en place :

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

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

Vous obtenez ainsi tous les index de la base de données avec leur instruction CREATE. Pour une seule table, PRAGMA index_list('products'); affiche uniquement les index de cette table, et PRAGMA index_info('idx_products_name'); indique les colonnes couvertes par chaque index. Tout ce qui commence par sqlite_autoindex_ a été créé automatiquement pour une contrainte PRIMARY KEY ou UNIQUE — impossible de les supprimer.

Quand ne pas ajouter d'index

Voici quelques cas où ajouter un index dégrade les performances :

• Petites tables. Quelques centaines de lignes se parcourent en microsecondes. De toute façon, le planificateur ignorera probablement l'index, et vous aurez ajouté un surcoût en écriture pour rien.
• Colonnes très sollicitées en écriture mais peu interrogées. Chaque écriture met à jour tous les index. Indexer une colonne sur laquelle vous filtrez rarement, c'est du pur coût.
• Colonnes à faible cardinalité prises isolément. Un index sur une colonne status avec trois valeurs possibles ne filtre pas grand-chose. Cela peut quand même servir comme deuxième colonne d'un index composite, ou en index partiel — mais seul, ça n'en vaut souvent pas la peine.
• Index déjà couvert. Si vous avez un index sur (a, b), inutile d'en créer un autre sur (a). SQLite exploite les colonnes de tête d'un index composite pour les requêtes qui filtrent uniquement sur a.

La vraie réponse à « est-ce que je dois ajouter cet index ? », c'est presque toujours la même : essayez, lancez EXPLAIN QUERY PLAN, mesurez avec des données réalistes, puis tranchez.

La suite : les index composites

Un index sur une seule colonne couvre beaucoup de cas, mais les requêtes réelles filtrent et trient souvent sur plusieurs colonnes à la fois. Les index composites — du type (a, b, c) — sont faits pour ça, et l'ordre des colonnes compte bien plus qu'on ne le croit. C'est l'objet de la page suivante.