Index parțial (CREATE INDEX … WHERE) în SQLite

CREATE [UNIQUE] INDEX nume ON tabel (coloane) WHERE conditie;

Ce face Index parțial (CREATE INDEX ... WHERE)

Un index parțial are o clauză WHERE: în el intră doar rândurile pentru care condiția este adevărată. Dacă din 100.000 de comenzi doar câteva sute sunt neplătite și le cauți des, un index doar pe comenzile neplătite este mult mai mic decât unul pe tot tabelul.

SQLite folosește indexul parțial doar când poate demonstra din WHERE-ul interogării că rândurile căutate sunt sigur în index. Cel mai simplu: interogarea conține aceeași condiție (de exemplu stare = 'neplatita'), scrisă la fel.

Cu UNIQUE, indexul parțial impune unicitatea doar pentru rândurile din index. Așa poți spune „un client are cel mult o adresă principală”, fără să limitezi adresele secundare. Condiția poate folosi doar coloane ale tabelului și funcții deterministe (fără subinterogări).

Parametri

Parametru	Descriere
conditie	Expresia care decide ce rânduri intră în index. Doar coloane din tabel, constante și funcții deterministe.

Exemple

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

CREATE TABLE comenzi (
  id INTEGER PRIMARY KEY,
  client_id INTEGER,
  total INTEGER,
  stare TEXT
);
INSERT INTO comenzi (client_id, total, stare) VALUES
  (1, 250, 'platita'),
  (2, 120, 'neplatita'),
  (1, 90, 'platita'),
  (3, 400, 'neplatita');

Exemplul 1: Index doar pe comenzile neplătite

Prima interogare conține condiția indexului și îl folosește. A doua caută comenzile plătite, care nu sunt în index, deci SQLite citește tot tabelul.

CREATE INDEX idx_neplatite ON comenzi (client_id) WHERE stare = 'neplatita';

EXPLAIN QUERY PLAN
SELECT id, total FROM comenzi WHERE stare = 'neplatita' AND client_id = 2;

EXPLAIN QUERY PLAN
SELECT id, total FROM comenzi WHERE stare = 'platita' AND client_id = 2;

Rezultat

QUERY PLAN
`--SEARCH comenzi USING INDEX idx_neplatite (client_id=?)
QUERY PLAN
`--SCAN comenzi

Exemplul 2: O singură adresă principală pe client

Indexul unic conține doar adresele cu principala = 1. Ana poate avea oricâte adrese secundare, dar o a doua adresă principală ar fi refuzată cu UNIQUE constraint failed.

CREATE TABLE adrese (
  client_id INTEGER,
  oras TEXT,
  principala INTEGER DEFAULT 0
);
CREATE UNIQUE INDEX idx_o_principala ON adrese (client_id) WHERE principala = 1;

INSERT INTO adrese VALUES (1, 'Iași', 1), (1, 'Pașcani', 0), (1, 'Vaslui', 0);
SELECT * FROM adrese ORDER BY principala DESC, oras;

Rezultat

╭───────────┬─────────┬────────────╮
│ client_id │  oras   │ principala │
╞═══════════╪═════════╪════════════╡
│         1 │ Iași    │          1 │
│         1 │ Pașcani │          0 │
│         1 │ Vaslui  │          0 │
╰───────────┴─────────┴────────────╯

Exemplul 3: Index care ignoră valorile NULL

Dacă o coloană este goală pentru majoritatea rândurilor, WHERE coloana IS NOT NULL ține în index doar rândurile completate. Pragma index_info arată coloana indexată.

CREATE TABLE facturi (id INTEGER PRIMARY KEY, cod_fiscal TEXT);
CREATE INDEX idx_cod_fiscal ON facturi (cod_fiscal) WHERE cod_fiscal IS NOT NULL;

SELECT name, partial FROM pragma_index_list('facturi');
SELECT name FROM pragma_index_info('idx_cod_fiscal');

Rezultat

╭────────────────┬─────────╮
│      name      │ partial │
╞════════════════╪═════════╡
│ idx_cod_fiscal │       1 │
╰────────────────┴─────────╯
╭────────────╮
│    name    │
╞════════════╡
│ cod_fiscal │
╰────────────╯

Greșeli frecvente

• Scrii condiția din interogare altfel decât în index (de exemplu stare IN ('neplatita') sau cu alt text). Dacă SQLite nu poate deduce că rândurile sunt în index, nu îl folosește.
• Pui în condiția indexului o funcție ca date('now'), ca să indexezi „comenzile de azi”. Funcțiile care depind de momentul rulării nu sunt permise în indexuri.

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 pe expresii: Indexează rezultatul unei expresii, de exemplu lower(email) sau un câmp JSON, ca să cauți rapid după valoarea calculată.
• EXPLAIN QUERY PLAN: Arată cum va executa SQLite o interogare: ce tabele citește în întregime (SCAN) și unde folosește un index (SEARCH).
• INSERT ... ON CONFLICT (upsert): Inserează un rând sau, dacă cheia există deja, actualizează rândul existent (ori nu face nimic), într-o singură comandă.