SQLite Index: CREATE INDEX richtig einsetzen

Was ein Index wirklich ist

Ein Index ist eine eigenständige Datenstruktur — ein sortierter B-Baum — mit dem SQLite Zeilen anhand eines Spaltenwerts findet, ohne die gesamte Tabelle zu durchsuchen. Ohne Index liest eine Abfrage wie WHERE email = 'rosa@example.com' jede einzelne Zeile und prüft sie. Mit einem Index auf email läuft SQLite in etwa log(n) Schritten durch den Baum und springt direkt zum Treffer.

Dieser Geschwindigkeitsgewinn ist aber nicht umsonst. Der Index ist eine Kopie der indizierten Spalte plus ein Verweis auf die Zeile. Jedes INSERT, jedes UPDATE einer indizierten Spalte und jedes DELETE muss den Index ebenfalls aktualisieren. Der Speicherbedarf steigt, der Schreibdurchsatz sinkt ein wenig. Der Deal lautet: Du zahlst beim Schreiben, sparst aber beim Lesen ein Vielfaches davon.

SQLite Index erstellen

Die grundlegende Syntax:

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

Namenskonvention: In den meisten Teams hat sich idx_<tabelle>_<spalte> eingebürgert, damit auf den ersten Blick klar ist, wozu der Index dient. Der Name muss in der gesamten Datenbank eindeutig sein – nicht nur innerhalb der Tabelle. Genau deshalb steckt der Tabellenname mit drin.

So entfernst du einen Index wieder:

DROP INDEX idx_users_email;

Indexes sind reines Performance-Gerüst. Ein DROP INDEX rührt deine Daten nie an – es ändert nur, wie schnell deine Queries laufen.

Unique Index in SQLite

Ein Unique Index hat einen doppelten Zweck: Er beschleunigt Lookups und stellt gleichzeitig sicher, dass kein Wert in der indizierten Spalte doppelt vorkommt.

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

Der dritte Insert schlägt mit UNIQUE constraint failed: accounts.username fehl. SQLite legt für PRIMARY KEY- und UNIQUE-Spalten automatisch unique Indizes an – du erkennst sie am Namen sqlite_autoindex_<table>_<n>. Ein eigenes CREATE UNIQUE INDEX brauchst du also nur dann, wenn die Einschränkung nicht direkt an der Tabelle deklariert wurde.

Was der Query Planner tatsächlich macht

Nur weil ein Index existiert, heißt das noch lange nicht, dass SQLite ihn auch verwendet. Der Query Planner entscheidet pro Abfrage neu, welche Strategie er fährt – und mit EXPLAIN QUERY PLAN siehst du genau, wofür er sich entschieden hat:

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;

Achte in der Ausgabe auf SEARCH ... USING INDEX idx_orders_customer – das bedeutet, der Index wird tatsächlich genutzt. Steht dort dagegen SCAN orders, hat der Planner einen Full Table Scan für günstiger gehalten (bei kleinen Tabellen oft die richtige Entscheidung) oder die Form deiner Query verhindert die Nutzung des Index. Wie man solche Pläne richtig liest, schauen wir uns gleich in einem eigenen Abschnitt an.

Wann ein SQLite-Index nicht genutzt wird

Indizes haben ein paar typische blinde Flecken. Jede der folgenden Situationen sorgt dafür, dass der Index auf email ignoriert wird:

-- Funktion umschließt die Spalte
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Führendes Platzhalterzeichen in LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Typkonflikt erzwingt eine Konvertierung
SELECT * FROM users WHERE email = 12345;

Der B-Baum ist nach dem rohen email-Wert sortiert. Sobald du die Spalte zur Query-Zeit transformierst, ist ein Full Scan unausweichlich. Mögliche Lösungen: Die Daten gleich normalisiert ablegen (etwa in einer Spalte email_lower), einen Expression Index anlegen (CREATE INDEX idx ON users(lower(email))) oder für Substring-Suchen die Volltextsuche von SQLite nutzen.

Covering Index in SQLite

Enthält ein Index bereits sämtliche Spalten, die deine Abfrage braucht, kann SQLite die Anfrage komplett aus dem Index beantworten – ohne die Tabelle überhaupt anzufassen. Genau das ist ein Covering Index. Der Kniff besteht darin, in der Index-Definition zusätzliche Spalten mit aufzunehmen:

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;

Da beide Spalten, die das Query benötigt, bereits im Index liegen, meldet SQLite USING COVERING INDEX – ein Zugriff auf die eigentliche Zeile entfällt. Covering Indexes gehören zu den wirkungsvollsten Optimierungen für stark frequentierte Lesepfade. Der Preis dafür: ein größerer Index. Mehrspaltige Indexe sind ein eigenes Kapitel für sich – darum kümmert sich das nächste Dokument.

SQLite Indexe auflisten und inspizieren

Es gibt zwei Wege, sich anzusehen, was vorhanden ist:

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

Damit bekommst du jede Index-Definition in der Datenbank inklusive CREATE-Statement. Für eine einzelne Tabelle zeigt dir PRAGMA index_list('products'); nur die Indexe genau dieser Tabelle, und PRAGMA index_info('idx_products_name'); verrät dir, welche Spalten der jeweilige Index abdeckt. Alles, was mit sqlite_autoindex_ anfängt, wurde automatisch für einen PRIMARY KEY oder eine UNIQUE-Constraint angelegt — solche Indexe lassen sich nicht löschen.

Wann du keinen Index anlegen solltest

Es gibt durchaus Fälle, in denen ein zusätzlicher Index die Sache eher verschlechtert:

• Sehr kleine Tabellen. Ein paar Hundert Zeilen sind in Mikrosekunden durchgescannt. Der Query Planner ignoriert den Index dann meist sowieso — du hast dir nur unnötigen Schreib-Overhead eingehandelt.
• Schreiblastige Spalten, die kaum abgefragt werden. Jeder Schreibvorgang aktualisiert jeden Index. Eine Spalte zu indizieren, nach der du fast nie filterst, ist reiner Mehraufwand ohne Nutzen.
• Spalten mit geringer Kardinalität, die alleine stehen. Ein Index auf einer status-Spalte mit drei möglichen Werten grenzt kaum etwas ein. Als zweite Spalte in einem zusammengesetzten Index oder als Partial Index kann das durchaus Sinn ergeben — alleinstehend lohnt es sich oft nicht.
• Bereits abgedeckt. Wenn du einen Index auf (a, b) hast, brauchst du keinen zusätzlichen auf (a). SQLite nutzt die führenden Spalten eines zusammengesetzten Index auch dann, wenn du nur nach a filterst.

Die ehrliche Antwort auf die Frage „Soll ich diesen Index anlegen?" lautet fast immer: ausprobieren, mit EXPLAIN QUERY PLAN analysieren, mit realistischen Daten messen, dann entscheiden.

Als Nächstes: Zusammengesetzte Indexe

Ein Index auf einer einzelnen Spalte deckt schon viel ab, aber echte Queries filtern und sortieren oft über mehrere Spalten gleichzeitig. Genau dafür gibt es zusammengesetzte Indexe — also Indexe auf (a, b, c) — und die Spaltenreihenfolge ist dabei wichtiger, als die meisten denken. Darum geht es auf der nächsten Seite.