SQLite Composite Index: Spaltenreihenfolge & Leftmost-Prefix

Ein Index, mehrere Spalten

Ein Composite Index – auch bekannt als Multi-Column-Index oder schlicht Index über mehrere Spalten – ist ein einzelner Index, der über zwei oder mehr Spalten aufgebaut wird. Du erstellst ihn, indem du die Spalten in der gewünschten Reihenfolge angibst:

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';

Der Index idx_orders_customer_status legt seine Einträge sortiert ab – zuerst nach customer_id, und innerhalb jedes Kunden dann nach status. Genau diese Sortierreihenfolge ist der Schlüssel zu allem: Jedes andere Verhalten eines Composite Index in SQLite ergibt sich direkt daraus.

Das mentale Modell: ein sortiertes Telefonbuch

Stell dir ein altes Telefonbuch vor. Die Einträge sind nach Nachname sortiert, und innerhalb desselben Nachnamens nach Vorname. Genau so sieht ein Index auf (last_name, first_name) aus.

Manche Abfragen sind dabei billig, andere nicht:

• „Finde alle mit Nachname Patel" – einfach, alle Patels stehen direkt beieinander.
• „Finde Priya Patel" – einfach, zu Patel springen und dann bis Priya weiterlesen.
• „Finde alle mit Vorname Priya" – langsam, du musst jede Seite durchblättern. Priyas verteilen sich quer über alle Nachnamen.

Ein SQLite Composite Index funktioniert nach demselben Prinzip. Die erste Spalte ist der primäre Sortierschlüssel; die zweite Spalte sortiert nur noch Einträge, die in der ersten Spalte denselben Wert haben.

Die Leftmost-Prefix-Regel in SQLite

SQLite kann einen Composite Index nur dann für eine Abfrage nutzen, wenn die WHERE-Klausel ein linkes Präfix seiner Spalten einschränkt. Für einen Index auf (a, b, c) heißt das:

• Filter auf a – Index wird genutzt.
• Filter auf a und b – Index wird genutzt.
• Filter auf a, b und c – Index wird genutzt.
• Filter nur auf b, nur auf c oder auf b und c – Index wird nicht genutzt.

Das lässt sich direkt mit EXPLAIN QUERY PLAN überprüfen:

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';

Der erste Plan zeigt SEARCH events USING INDEX idx_events_user_kind_time. Der zweite fällt auf SCAN events zurück – wer nur nach kind filtert, überspringt die führende Spalte user_id, und damit ist der Index für diese Query wertlos.

Spaltenreihenfolge im SQLite Composite Index ist eine Designentscheidung

Weil die Leftmost-Prefix-Regel greift, ist die Reihenfolge der Spalten in CREATE INDEX eine echte Entscheidung – keine Frage des Geschmacks. Zwei Faustregeln helfen dir dabei:

1. Die Spalte, nach der du am häufigsten filterst, gehört nach vorn. Sie öffnet den Index für die größte Bandbreite an Queries.
2. Gleichheitsspalten vor Bereichsspalten. SQLite kann sich per = in den Index reinhangeln und danach mit <, > oder BETWEEN einen zusammenhängenden Bereich durchscannen – aber nur auf der letzten genutzten Spalte.

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

-- Gut: Gleichheit auf region zuerst, Bereich auf sold_at danach.
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';

Der Plan zeigt SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite springt direkt zu region = 'EU' und läuft dann durch den Datumsbereich nach vorne. Drehst du die Spaltenreihenfolge zu (sold_at, region) um, muss dieselbe Abfrage jede Zeile im Datumsbereich scannen und für jede einzelne region erneut prüfen.

Composite Index vs. mehrere Single-Column-Indexe

Eine Frage, die immer wieder auftaucht: Soll ich lieber einen Index auf (a, b) anlegen oder zwei separate Indexe auf a und b?

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

-- Option A: ein zusammengesetzter Index.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Option B (Alternative): zwei einspaltige Indexe.
-- 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';

Beim kombinierten Filter ist der Composite Index schneller — SQLite springt direkt zu den passenden (project_id, state)-Einträgen. Mit zwei einzelnen Indizes auf jeweils einer Spalte wählt SQLite normalerweise einen davon aus, grenzt damit die Zeilen ein und prüft dann bei jeder Trefferzeile die andere Spalte nach. Manchmal werden beide Indizes auch miteinander verschnitten, aber wenn die Spalten zusammen abgefragt werden, ist der Composite Index die saubere Lösung.

Werden project_id und state zusätzlich auch einzeln abgefragt, kann es sinnvoll sein, beides anzulegen — den Composite Index für die kombinierte Abfrage und einen einspaltigen Index auf state für Queries, die nur danach filtern.

Covering Index in SQLite

Enthält ein Index alle Spalten, die eine Query braucht — sowohl die Filterspalten als auch die ausgewählten Spalten — kann SQLite die Abfrage komplett aus dem Index beantworten, ohne die Tabelle überhaupt anzufassen. Das nennt man einen Covering Index, und schneller geht's nicht.

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, 'erste'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'eilig');

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

Der Ausführungsplan zeigt USING COVERING INDEX idx_invoices_cover. Die Query liest issued_at und total direkt aus dem Index — notes und id werden gar nicht gebraucht, also wird die Tabelle selbst überhaupt nicht angefasst. Eine Spalte ausschließlich deshalb in einen Composite Index aufzunehmen, um eine viel genutzte Query als Covering Index abzudecken, ist ein lohnender Deal, wenn diese Query ständig läuft.

Zusammengesetzte UNIQUE-Constraints

Ein SQLite Composite Index kann auch Eindeutigkeit über mehrere Spalten hinweg erzwingen. Praktisch immer dann, wenn keine einzelne Spalte für sich genommen eindeutig ist, die Kombination aber sehr wohl eindeutig sein muss:

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, anderer Kurs
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- schlägt fehl: Paar wiederholt sich

Beim dritten Insert schlägt SQLite mit UNIQUE constraint failed: enrollments.student_id, enrollments.course_id fehl. Das Paar existiert bereits im Index, deshalb lehnt SQLite das Duplikat ab.

Stolperfallen, die du kennen solltest

• OR zwischen Nicht-Führungsspalten blockiert den Index. Bei einem Index (a, b) kann WHERE a = 1 OR b = 2 den Index in der Regel gar nicht nutzen — SQLite muss beide Zweige getrennt betrachten.
• Funktionen auf indizierten Spalten machen den Index unbrauchbar. WHERE lower(email) = 'x' nutzt keinen Index auf email. Indexiere stattdessen den Ausdruck selbst oder normalisiere die Daten schon beim Einfügen.
• Indizes sind nicht umsonst. Jeder Index wird bei jedem INSERT, jedem UPDATE (auf indizierten Spalten) und jedem DELETE mit aktualisiert. Drei Composite Indexes auf einer schreibintensiven Tabelle können die Schreibkosten dominieren.
• Nach dem Anlegen von Indizes ANALYZE ausführen. Der Planner von SQLite nutzt die Statistiken, die ANALYZE sammelt, um zwischen Indexkandidaten zu wählen. Ohne diese Statistiken greift er auf Heuristiken zurück, die nicht immer optimal sind.

Ein praxistauglicher Workflow

Beim Tuning einer langsamen Abfrage sieht die Schleife meist so aus:

1. EXPLAIN QUERY PLAN auf die Abfrage anwenden, um zu sehen, was SQLite aktuell tut.
2. Wenn gescannt wird: schau dir die WHERE-Klausel an — welche Spalte ist die Gleichheitsspalte? Welche der Range-Filter? Was wird ausgewählt?
3. Einen Composite Index bauen, in der Reihenfolge erst Gleichheit, dann Range, und bei Bedarf die ausgewählten Spalten anhängen, falls ein Covering Index hilft.
4. ANALYZE ausführen.
5. EXPLAIN QUERY PLAN erneut laufen lassen. Bestätige, dass sich der Plan geändert hat und der Index tatsächlich genutzt wird.
6. Die Abfrage vor und nach der Änderung mit realistischen Daten messen.

Schritt 6 zu überspringen ist riskant. Ein Index, der im Plan korrekt aussieht, kann in der Praxis trotzdem langsamer sein — etwa wenn die Tabelle klein ist oder der Planner einen anderen Weg wählt.

Als Nächstes: Partial Indexes

Composite Indexes decken jede Zeile der Tabelle ab. Oft interessiert dich aber nur ein kleiner Teil der Zeilen — offene Tickets, unbearbeitete Jobs, nicht gelöschte Datensätze. Mit einem Partial Index kannst du genau diese Zeilen indizieren, indem du eine WHERE-Klausel direkt in den Index einbaust. Darum geht es auf der nächsten Seite.