Index composites SQLite : ordre des colonnes et préfixe gauche

Un seul index, plusieurs colonnes

Un index composite — qu'on appelle aussi index multi-colonnes — est un index unique construit à partir de deux colonnes ou plus. Pour le créer, il suffit d'énumérer les colonnes dans l'ordre voulu :

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

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

L'index idx_orders_customer_status range ses entrées triées d'abord par customer_id, puis par status à l'intérieur de chaque client. Tout part de cet ordre — le reste du fonctionnement des index composites en découle directement.

Le bon modèle mental : un annuaire trié

Imaginez un vieil annuaire papier. Les entrées sont classées par nom de famille, puis par prénom à l'intérieur de chaque nom. C'est exactement la tête qu'a un index sur (last_name, first_name).

Certaines recherches sont rapides, d'autres beaucoup moins :

• « Trouver tous les Patel » — facile, tous les Patel sont regroupés.
• « Trouver Priya Patel » — facile, on saute à Patel, puis on parcourt jusqu'à Priya.
• « Trouver tous les Priya » — lent, il faut parcourir toutes les pages. Les Priya sont dispersés sur tous les noms de famille.

Un index composite SQLite fonctionne pareil. La première colonne sert de clé de tri principale ; la deuxième colonne ne trie que les entrées qui partagent la même valeur sur la première colonne.

La règle du préfixe gauche en SQLite

SQLite ne peut exploiter un index multi-colonnes que si la clause WHERE contraint un préfixe gauche de ses colonnes. Pour un index sur (a, b, c) :

• Filtre sur a — l'index est utilisé.
• Filtre sur a et b — l'index est utilisé.
• Filtre sur a, b et c — l'index est utilisé.
• Filtre sur b seul, sur c seul, ou sur b et c — l'index n'est pas utilisé.

Tu peux le vérifier directement avec EXPLAIN QUERY PLAN :

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

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

Le premier plan affiche SEARCH events USING INDEX idx_events_user_kind_time. Le second retombe sur un SCAN events — filtrer uniquement sur kind saute la colonne de tête user_id, donc l'index ne sert à rien pour cette requête.

L'ordre des colonnes est un choix de conception

Puisque le préfixe gauche est déterminant, l'ordre dans lequel vous listez les colonnes dans CREATE INDEX relève d'une vraie décision, pas d'un détail cosmétique. Deux règles de base :

1. Placez en premier la colonne sur laquelle vous filtrez le plus souvent. C'est elle qui ouvre l'index au plus grand nombre de requêtes.
2. Mettez les colonnes d'égalité avant les colonnes de plage. SQLite peut plonger dans l'index via un =, puis parcourir une plage contiguë avec <, > ou BETWEEN — mais seulement sur la dernière colonne utilisée.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- Bien : égalité sur region en premier, plage sur sold_at en second.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

Le plan affiche SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite saute directement à region = 'EU', puis avance dans la plage de dates. Inversez l'ordre des colonnes en (sold_at, region) et la même requête devra parcourir toutes les lignes de la plage de dates en revérifiant region à chaque fois.

Index composite ou plusieurs index mono-colonnes ?

Une question revient souvent : vaut-il mieux créer un seul index sur (a, b), ou bien deux index distincts sur a et sur b ?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- Option A : un seul index composite.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Option B (alternative) : deux index mono-colonne.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

Pour le filtre combiné, l'index composite gagne haut la main : SQLite saute directement aux entrées (project_id, state) qui correspondent. Avec deux index mono-colonne, SQLite va généralement en choisir un, l'utiliser pour réduire le nombre de lignes, puis revérifier l'autre colonne sur chaque ligne restante. Il arrive qu'il fasse une intersection des deux, mais quand les colonnes sont interrogées ensemble, l'index multi-colonnes reste la solution la plus propre.

Si project_id et state sont aussi interrogés séparément, mieux vaut peut-être avoir les deux : l'index composite pour le filtre combiné, et un index mono-colonne sur state pour les requêtes qui ne portent que sur cette colonne.

Les index couvrants

Quand un index contient toutes les colonnes dont une requête a besoin — aussi bien celles du filtre que celles renvoyées dans le SELECT — SQLite peut répondre sans jamais toucher à la table. C'est ce qu'on appelle un index couvrant, et il n'y a pas plus rapide.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'première'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'urgent');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

Le plan affiche USING COVERING INDEX idx_invoices_cover. La requête récupère issued_at et total directement depuis l'index — notes et id ne sont pas nécessaires, donc la table elle-même n'est jamais ouverte. Ajouter une colonne à un index composite uniquement pour couvrir une requête critique est un compromis intéressant quand cette requête tourne en permanence.

Contraintes UNIQUE composites

Les index multi-colonnes servent aussi à imposer l'unicité sur une combinaison de colonnes. Pratique quand aucune colonne prise isolément n'est unique, mais que leur combinaison doit l'être :

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- ok, cours différent
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- échec : la paire se répète

Le troisième INSERT déclenche UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. La paire existe déjà dans l'index, donc SQLite refuse le doublon.

Pièges à connaître

• Un OR entre colonnes non-principales casse l'index. WHERE a = 1 OR b = 2 sur un index (a, b) ne pourra généralement pas exploiter l'index — SQLite doit traiter chaque branche séparément.
• Une fonction appliquée à une colonne indexée désactive l'index. WHERE lower(email) = 'x' n'utilisera pas un index posé sur email. La parade : indexer directement l'expression, ou normaliser la donnée à l'insertion.
• Un index, ça coûte. Chaque index est mis à jour à chaque INSERT, UPDATE (sur les colonnes indexées) et DELETE. Sur une table avec beaucoup d'écritures, trois index composites peuvent vite plomber les perfs en écriture.
• Lancez ANALYZE après avoir créé vos index. Le planificateur de SQLite s'appuie sur les statistiques collectées par ANALYZE pour départager les index candidats. Sans ces stats, il retombe sur des heuristiques qui ne sont pas toujours pertinentes.

Une démarche concrète pour optimiser une requête sqlite

Pour ajuster une requête lente, la boucle ressemble en général à ceci :

1. Lancez EXPLAIN QUERY PLAN sur la requête pour voir ce que fait SQLite aujourd'hui.
2. S'il y a un scan, regardez la clause WHERE : quelle est la colonne d'égalité ? Quelle est la colonne de plage ? Que sélectionne-t-on ?
3. Construisez un index composite avec l'égalité en premier, la plage ensuite, et ajoutez les colonnes sélectionnées si un index couvrant peut aider.
4. Exécutez ANALYZE.
5. Relancez EXPLAIN QUERY PLAN. Vérifiez que le plan a changé et que l'index est bien utilisé.
6. Chronométrez la requête avant et après, sur des données représentatives.

Sautez l'étape 6 à vos risques et périls. Un index qui paraît correct dans le plan peut très bien être plus lent en pratique, si la table est petite ou si le planificateur choisit un autre chemin.

La suite : les index partiels

Un index composite couvre toutes les lignes de la table. Mais souvent, seule une petite portion des lignes nous intéresse vraiment — les tickets ouverts, les jobs non traités, les enregistrements non supprimés. Un index partiel permet d'indexer uniquement ces lignes-là, avec une clause WHERE intégrée à l'index lui-même. C'est ce qu'on voit sur la page suivante.