CREATE INDEX în SQLite: explicație și exemple

Ce face `CREATE INDEX`

Un index este o structură separată (un arbore B) care ține valorile unei coloane sortate, fiecare cu rowid-ul rândului ei. Când cauți WHERE oras = 'Iași', SQLite coboară în index și sare direct la rândurile potrivite, în loc să parcurgă tot tabelul.

SQLite creează singur un index pentru fiecare PRIMARY KEY care nu este INTEGER PRIMARY KEY și pentru fiecare constrângere UNIQUE; aceste indexuri automate au nume de forma sqlite_autoindex_tabel_1. Pentru celelalte coloane după care filtrezi, sortezi sau legi tabele, indexul îl creezi tu.

Un index pe mai multe coloane (a, b) ajută interogările care filtrează după a, sau după a și b, dar nu pe cele care filtrează doar după b. Fiecare index ocupă loc în fișier și încetinește puțin INSERT, UPDATE și DELETE, pentru că trebuie actualizat și el. Ce index alege SQLite vezi cu EXPLAIN QUERY PLAN.

Parametri

Parametru	Descriere
`UNIQUE`	Indexul refuză două rânduri cu aceeași valoare (valorile `NULL` nu se consideră egale între ele).
`IF NOT EXISTS`	Nu dă eroare dacă indexul există deja.
`nume`	Numele indexului, unic în toată baza de date (nu doar în tabel).
`coloana ASC \| DESC`	Coloanele indexate, în ordine. Ordinea contează pentru indexurile pe mai multe coloane.

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE TABLE clienti (
  id INTEGER PRIMARY KEY,
  nume TEXT NOT NULL,
  email TEXT,
  oras TEXT
);
INSERT INTO clienti (nume, email, oras) VALUES
  ('Ana Popescu', 'Ana@Exemplu.ro', 'Iași'),
  ('Mihai Ionescu', 'mihai@exemplu.ro', 'Cluj'),
  ('Elena Dima', 'elena@exemplu.ro', 'Iași');

Exemplul 1: Înainte și după index

Fără index, SQLite citește tot tabelul (SCAN). După CREATE INDEX, aceeași interogare folosește indexul (SEARCH ... USING INDEX).

EXPLAIN QUERY PLAN
SELECT nume FROM clienti WHERE oras = 'Iași';

CREATE INDEX idx_clienti_oras ON clienti (oras);

EXPLAIN QUERY PLAN
SELECT nume FROM clienti WHERE oras = 'Iași';

Rezultat

QUERY PLAN
`--SCAN clienti
QUERY PLAN
`--SEARCH clienti USING INDEX idx_clienti_oras (oras=?)

Exemplul 2: Ce indexuri are un tabel

Indexurile sunt păstrate în tabelul de sistem sqlite_schema. Constrângerea UNIQUE a creat singură indexul sqlite_autoindex_utilizatori_1, care nu are text SQL (sql este NULL).

CREATE TABLE utilizatori (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE,
  oras TEXT
);
CREATE INDEX idx_utilizatori_oras ON utilizatori (oras);

SELECT name, tbl_name, sql
FROM sqlite_schema
WHERE type = 'index'
ORDER BY name;

Rezultat

╭────────────────────────────────┬─────────────┬─────────────────────────────────────────────────────────╮
│              name              │  tbl_name   │                           sql                           │
╞════════════════════════════════╪═════════════╪═════════════════════════════════════════════════════════╡
│ idx_utilizatori_oras           │ utilizatori │ CREATE INDEX idx_utilizatori_oras ON utilizatori (oras) │
│ sqlite_autoindex_utilizatori_1 │ utilizatori │                                                         │
╰────────────────────────────────┴─────────────┴─────────────────────────────────────────────────────────╯

Exemplul 3: UNIQUE INDEX refuză dublurile

Un index unic se comportă ca o constrângere UNIQUE adăugată după crearea tabelului. Al doilea client cu același e-mail este refuzat.

CREATE UNIQUE INDEX idx_clienti_email ON clienti (email);
INSERT INTO clienti (nume, email, oras) VALUES ('Ion Rusu', 'mihai@exemplu.ro', 'Iași');

Rezultat (eroare)

Error near line 2: UNIQUE constraint failed: clienti.email

Greșeli frecvente

Creezi indexuri pe toate coloanele „ca să fie”. Fiecare index ocupă spațiu și încetinește scrierile; creează-le doar pentru coloanele folosite în WHERE, JOIN sau ORDER BY.
Te aștepți ca indexul pe (oras, nume) să ajute o căutare doar după nume. Indexul se folosește de la stânga la dreapta: fără o condiție pe oras, nu ajută.
Compari o coloană indexată printr-o funcție: WHERE lower(email) = '...' nu folosește indexul pe email. Pentru asta există indexurile pe expresii.

Comenzi înrudite

EXPLAIN QUERY PLAN: Arată cum va executa SQLite o interogare: ce tabele citește în întregime (SCAN) și unde folosește un index (SEARCH).
Index parțial (CREATE INDEX ... WHERE): Un index care conține doar rândurile care îndeplinesc o condiție: mai mic, mai rapid de actualizat și util pentru reguli de unicitate.
Index pe expresii: Indexează rezultatul unei expresii, de exemplu lower(email) sau un câmp JSON, ca să cauți rapid după valoarea calculată.
rowid și INTEGER PRIMARY KEY: Orice tabel SQLite obișnuit are o coloană ascunsă rowid; o coloană INTEGER PRIMARY KEY devine alias al ei și primește automat valori.
VACUUM: Reconstruiește fișierul bazei de date: eliberează spațiul rămas după ștergeri și, cu INTO, face o copie compactă.

Ce face CREATE INDEX