Index pe expresii în SQLite: explicație și exemple

Ce face `Index pe expresii`

În locul unei coloane, poți indexa o expresie: lower(email), pret * stoc, json_extract(date, '$.oras'). SQLite calculează expresia pentru fiecare rând și o ține sortată în index.

Indexul este folosit doar când interogarea conține exact aceeași expresie, în WHERE sau ORDER BY. lower(email) și LOWER(email) sunt aceeași expresie, dar lower(trim(email)) nu mai este.

Expresia poate folosi doar coloanele tabelului și funcții deterministe (care dau mereu același rezultat pentru aceleași date). random() sau subinterogările nu sunt permise.

Parametri

Parametru	Descriere
`expresie`	Orice expresie deterministă construită din coloanele tabelului.

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: Căutare după e-mail fără să conteze literele mari

Ana și-a scris adresa cu litere mari. Indexul pe lower(email) găsește rândul indiferent cum a fost scris, iar planul arată că se folosește indexul.

CREATE INDEX idx_email_mic ON clienti (lower(email));

SELECT nume FROM clienti WHERE lower(email) = 'ana@exemplu.ro';

EXPLAIN QUERY PLAN
SELECT nume FROM clienti WHERE lower(email) = 'ana@exemplu.ro';

Rezultat

╭─────────────╮
│    nume     │
╞═════════════╡
│ Ana Popescu │
╰─────────────╯
QUERY PLAN
`--SEARCH clienti USING INDEX idx_email_mic (<expr>=?)

Exemplul 2: Index pe un câmp dintr-o coloană JSON

Datele fiecărui eveniment stau în JSON. Indexul pe date ->> '$.oras' permite căutarea rapidă după oraș, dacă interogarea folosește aceeași expresie.

CREATE TABLE evenimente (id INTEGER PRIMARY KEY, date TEXT);
INSERT INTO evenimente (date) VALUES
  ('{"tip": "comanda", "oras": "Iași"}'),
  ('{"tip": "retur", "oras": "Cluj"}');
CREATE INDEX idx_ev_oras ON evenimente (date ->> '$.oras');

EXPLAIN QUERY PLAN
SELECT id FROM evenimente WHERE date ->> '$.oras' = 'Iași';

Rezultat

QUERY PLAN
`--SEARCH evenimente USING COVERING INDEX idx_ev_oras (<expr>=?)

Exemplul 3: Funcțiile nedeterministe sunt refuzate

random() dă alt rezultat la fiecare apel, deci nu poate fi indexat.

CREATE INDEX idx_gresit ON clienti (random());

Rezultat (eroare)

Parse error near line 1: non-deterministic functions prohibited in index expressions
  CREATE INDEX idx_gresit ON clienti (random());
                        error here ---^

Greșeli frecvente

Creezi indexul pe lower(email), dar interogarea folosește email = ... sau upper(email). Expresia trebuie să fie aceeași.
Folosești COLLATE NOCASE și un index pe lower() amestecat. Alege un singur mod: fie coloana declarată COLLATE NOCASE cu index obișnuit, fie indexul pe lower(...). Atenție: ambele ignoră doar literele mari ASCII, nu diacriticele.

Comenzi înrudite

CREATE INDEX: Creează un index pe una sau mai multe coloane, ca SQLite să găsească rândurile fără să citească tot tabelul.
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.
Operatorii -> și ->>: Prescurtări pentru citirea din JSON: -> întoarce o bucată de JSON, ->> întoarce valoarea SQL, fără ghilimele.
Coloane generate (GENERATED ALWAYS AS): O coloană a cărei valoare este calculată automat dintr-o expresie peste celelalte coloane ale rândului, la citire sau la scriere.

Ce face Index pe expresii