SQLite Partial Index: CREATE INDEX ... WHERE

Ein Partial Index deckt nur einen Teil der Zeilen ab

Ein normaler Index hat für jede Zeile der Tabelle einen Eintrag. Ein Partial Index dagegen enthält nur Einträge für die Zeilen, die zu einer WHERE-Bedingung passen, die du beim Anlegen mitgibst. Das Ergebnis: kleinerer Index, weniger Seiten zum Durchlaufen und weniger Aufwand bei jedem Insert oder Update, das den indizierten Ausschnitt gar nicht berührt.

Die Syntax ist ein ganz normales CREATE INDEX mit angehängtem WHERE:

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 enthält ausschließlich Einträge für Zeilen mit status = 'pending'. Versendete, stornierte oder erstattete Bestellungen tauchen darin gar nicht erst auf. Wenn 95 % deiner orders-Tabelle aus Altbeständen besteht und du hauptsächlich die offenen Bestellungen abfragst, hast du damit einen 20× kleineren Index bei gleicher Abfragegeschwindigkeit.

Wann der Query Planner den partial index tatsächlich nutzt

Ein gefilterter Index ist nur dann verwendbar, wenn SQLite beweisen kann, dass deine Abfrage genau auf die Zeilen einschränkt, die der Index abdeckt. Am saubersten klappt das, indem du die WHERE-Klausel des Index in der Abfrage wiederholst:

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;

Der Ausführungsplan sollte USING INDEX idx_orders_pending enthalten. Sobald du status = 'pending' aus der Query streichst, fällt der Planner auf einen vollständigen Tabellenscan zurück — er kann ja nicht wissen, dass die Abfrage innerhalb der indizierten Teilmenge bleibt.

Faustregel: Das WHERE der Query muss das WHERE des Index implizieren. Gleichheit auf dieselbe Spalte mit demselben Wert ist der einfache, offensichtliche Fall. Bei Ungleichheiten und OR wird's haarig — prüf das im Zweifel mit EXPLAIN QUERY PLAN.

Wann sich ein Partial Index lohnt — drei klare Vorteile

Drei handfeste Gründe, warum sich ein gefilterter Index in SQLite auszahlt:

1. Weniger Platz auf der Platte. Es werden nur die passenden Zeilen gespeichert. Bei einem Workload nach dem Motto „1 % der Tabelle ist heiß" ist der Index etwa 1 % so groß wie ein vollständiger.
2. Günstigere Schreibvorgänge. Inserts und Updates fassen den Index nur an, wenn die Zeile zum Filter passt. Ein Insert mit status = 'shipped' auf der obigen Tabelle rührt idx_orders_pending schlicht nicht an.
3. Gleich schnelle Lookups. Ein B-Tree-Lookup ist logarithmisch zur Indexgröße. Kleinerer Index heißt minimal schnellere Suchen — der eigentliche Gewinn liegt aber drumherum: weniger Cache-Misses, weniger I/O.

Wenn eine Spalte stark schiefverteilt ist — die meisten Zeilen haben denselben Wert und dich interessieren nur die seltenen Ausnahmen — ist das der Lehrbuch-Anwendungsfall für einen Partial Index.

Partial Unique Index in SQLite (das Killer-Feature)

Ein normales UNIQUE-Constraint gilt für jede einzelne Zeile. Spätestens beim Thema Soft Delete wird genau das zum Problem:

-- Schlägt fehl: Es gibt zwei Zeilen mit email = 'a@x.com', auch wenn eine gelöscht ist.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Mit einem partial unique index kannst du die Eindeutigkeit gezielt nur auf die Zeilen anwenden, die wirklich relevant sind:

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'),  -- soft-gelöscht
    ('a@x.com', '2026-01-05'),  -- erneut soft-gelöscht
    ('a@x.com', NULL);          -- eine aktive Zeile, erlaubt

SELECT email, deleted_at FROM users;

Drei Zeilen, dieselbe E-Mail, kein Constraint-Verstoß – weil nur die Zeile mit deleted_at IS NULL in die Eindeutigkeitsprüfung einfließt. Sobald du eine zweite aktive Zeile mit derselben E-Mail einfügen willst, wirft SQLite ein UNIQUE constraint failed.

Dieses Muster begegnet dir ständig: ein aktives Abo pro Kunde, eine primäre Adresse pro Nutzer, eine offene Rechnung pro Bestellung. Mit einem partial unique index in SQLite drückst du genau das aus – direkt und ohne Umwege.

Indexieren rund um NULL

NULL verhält sich in Indexen etwas eigenwillig. Häufig willst du die NULL-Werte schlicht ignorieren – etwa bei einer dünn besetzten Spalte external_id, in der die meisten Zeilen NULL sind, die belegten Werte aber eindeutig bleiben müssen:

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
    ('Widget',  'EXT-001'),
    ('Gadget',  NULL),
    ('Doohickey', NULL),
    ('Sprocket', 'EXT-002');

SELECT name, external_id FROM products;

Zwei NULL-Werte vertragen sich problemlos, während die Zeilen EXT-001 und EXT-002 garantiert eindeutig bleiben. Der Index fällt zudem kleiner aus – NULL-Zeilen landen gar nicht erst darin – sodass Lookups nach external_id auch bei wachsender Tabelle flott bleiben.

Was darf in der Filterbedingung stehen?

Die WHERE-Klausel eines Partial Index in SQLite ist ziemlich restriktiv. Erlaubt sind:

• Spalten der Tabelle, die indiziert wird.
• Literale Konstanten.
• Eine kleine Auswahl deterministischer Built-in-Funktionen.

Nicht erlaubt sind dagegen:

• Andere Tabellen.
• Unterabfragen.
• Nicht-deterministische Funktionen wie random() oder CURRENT_TIMESTAMP.
• Parameter oder Variablen.

Das ergibt durchaus Sinn: SQLite muss den Filter bei jedem INSERT und UPDATE neu auswerten, und das Ergebnis muss reproduzierbar sein. Folgendes funktioniert daher problemlos:

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

Aber WHERE created_at > date('now') funktioniert nicht — date('now') ändert sich ja ständig, und damit würde sich die Menge der indizierten Zeilen quasi unter SQLite wegbewegen.

Ein kurzer Sanity-Check

Wenn du einen Partial Index anlegst, geh am besten diese drei Punkte durch:

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. Wählt der Planer den Index aus?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Ignoriert er Abfragen außerhalb des Filters?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Überspringt er Abfragen, denen der Filter komplett fehlt?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

Query 1 sollte idx_jobs_runnable benutzen. Queries 2 und 3 fallen auf einen Scan zurück (oder auf einen anderen Index, falls vorhanden). Wenn der Planner den partial Index für eine Query wählt, bei der du es nicht erwartet hast, lies den Filter nochmal genau – meist ist er breiter, als du denkst.

Wann ein partial Index keinen Sinn ergibt

Partial Indexes sind ein scharfes Werkzeug. Gründe, lieber darauf zu verzichten:

• Der Filter trifft auf den Großteil der Tabelle zu. Wenn 90 % deiner Zeilen "active" sind, ist ein partial Index am Ende nur ein normaler Index mit Umweg. Dann indexiere einfach die Spalte direkt.
• Deine Queries enthalten den Filter nicht wörtlich. Wenn dein Code über ein ORM läuft, das WHERE status IN (?, ?, ?) baut, oder den Filter dynamisch zusammenbaut, erkennt der Planner die Übereinstimmung oft nicht. Teste mit EXPLAIN QUERY PLAN, statt zu raten.
• Die heiße Teilmenge verschiebt sich mit der Zeit. Ein partial Index auf "Bestellungen der letzten 30 Tage" klingt verlockend, lässt sich aber so nicht ausdrücken – der Filter muss deterministisch sein. Du müsstest den Index regelmäßig neu aufbauen oder ein anderes Schema wählen (eine separate Tabelle recent_orders oder ein Flag archived, das du nachts umlegst).

Wenn der Filter stabil ist und nur einen kleinen Ausschnitt einer großen Tabelle trifft, gehören partial Indexes zu den wirkungsvollsten Tuning-Tricks, die SQLite bietet.

Als Nächstes: Query Pläne lesen

Auf dieser Seite haben wir uns immer wieder auf EXPLAIN QUERY PLAN verlassen, um zu prüfen, ob ein Index tatsächlich genutzt wird. Dieses Werkzeug verdient eine eigene Seite – wie du seine Ausgabe liest, was die Keywords bedeuten und wie du einen sauberen Index-Lookup von einem heimlichen Full Scan unterscheidest. Das kommt als Nächstes.