Indici parziali in SQLite: CREATE INDEX ... WHERE

Un indice parziale copre solo alcune righe

Un indice normale ha una voce per ogni riga della tabella. Un indice parziale ha voci solo per le righe che soddisfano una clausola WHERE che indichi quando lo crei. Indice più piccolo, meno pagine da percorrere, meno lavoro a ogni insert e update che non tocca la porzione indicizzata.

La sintassi è una normale CREATE INDEX con un WHERE in coda:

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 contiene voci solo per le righe in cui status = 'pending'. Gli ordini spediti, annullati e rimborsati non ci sono proprio. Se il 95% della tua tabella orders è storico e interroghi soprattutto quelli aperti, hai un indice 20 volte più piccolo con la stessa velocità di query.

Quando il planner lo usa davvero

Un indice parziale è utilizzabile solo quando SQLite riesce a dimostrare che la tua query si limita alle stesse righe coperte dall'indice. Il modo più pulito è ripetere nella query la clausola WHERE dell'indice:

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;

Il piano dovrebbe citare USING INDEX idx_orders_pending. Togli status = 'pending' dalla query e il planner torna a una scansione completa della tabella: non ha modo di sapere che la query resta dentro il sottoinsieme indicizzato.

La regola pratica: il WHERE della query deve implicare il WHERE dell'indice. L'uguaglianza sulla stessa colonna e sullo stesso valore è il caso sicuro e ovvio. Disuguaglianze e OR complicano le cose; verifica con EXPLAIN QUERY PLAN.

Perché ne vale la pena: i tre vantaggi

Tre motivi concreti per cui gli indici parziali ripagano:

1. Più piccoli su disco. Vengono salvate solo le righe che corrispondono. Con un carico in cui "l'1% della tabella è caldo", l'indice è circa l'1% di uno completo.
2. Scritture più economiche. Insert e update toccano l'indice solo quando la riga soddisfa il filtro. Un insert con status = 'shipped' nella tabella qui sopra non tocca affatto idx_orders_pending.
3. Stessa velocità di ricerca. Una ricerca in un B-tree è logaritmica rispetto alla dimensione dell'indice. Indice più piccolo, ricerche un po' più veloci, ma il guadagno maggiore sta in tutto il resto: meno cache miss, meno I/O.

Se una colonna è molto sbilanciata, con la maggior parte delle righe su un valore e un interesse solo per i valori rari, sei davanti al caso da manuale per un indice parziale.

Indici unique parziali (la funzione vincente)

I normali vincoli UNIQUE valgono per ogni riga. È un problema non appena introduci il soft delete:

-- Fallisce: ci sono due righe con email = 'a@x.com', anche se una è eliminata.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Un indice unique parziale ti permette di imporre l'unicità solo sulle righe che contano:

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'),  -- eliminata in modo logico
    ('a@x.com', '2026-01-05'),  -- eliminata di nuovo
    ('a@x.com', NULL);          -- una riga attiva, consentita

SELECT email, deleted_at FROM users;

Tre righe, stessa email, nessuna violazione del vincolo: solo la riga con deleted_at IS NULL partecipa al controllo di unicità. Prova a inserire una seconda riga attiva con la stessa email e SQLite solleva UNIQUE constraint failed.

Questo schema si trova ovunque: un solo abbonamento attivo per cliente, un solo indirizzo principale per utente, una sola fattura aperta per ordine. Gli indici unique parziali lo esprimono direttamente.

Indicizzare tenendo conto dei NULL

NULL ha un rapporto strano con gli indici. Un obiettivo comune è "ignorare del tutto i NULL": per esempio hai una colonna external_id poco popolata, in cui la maggior parte delle righe è NULL ma quelle valorizzate devono essere uniche:

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
    ('Aggeggio',  'EXT-001'),
    ('Arnese',  NULL),
    ('Coso', NULL),
    ('Ingranaggio', 'EXT-002');

SELECT name, external_id FROM products;

I due NULL convivono senza problemi; le righe EXT-001 e EXT-002 sono garantite uniche. L'indice è anche più piccolo, dato che le righe NULL non vengono salvate affatto, quindi le ricerche per external_id restano veloci anche quando la tabella cresce.

Cosa può usare il filtro

La clausola WHERE di un indice parziale è restrittiva. Può fare riferimento a:

• Colonne della tabella indicizzata.
• Costanti letterali.
• Un piccolo insieme di funzioni integrate deterministiche.

Non può fare riferimento a:

• Altre tabelle.
• Subquery.
• Funzioni non deterministiche come random() o CURRENT_TIMESTAMP.
• Parametri o variabili.

Ha senso: SQLite deve valutare il filtro a ogni insert e update di una riga, e il risultato deve essere stabile. Quindi questo funziona:

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

Ma WHERE created_at > date('now') no: date('now') cambia nel tempo, quindi l'insieme delle righe indicizzate si sposterebbe sotto i piedi di SQLite.

Un controllo di verifica

Quando aggiungi un indice parziale, fai tre controlli:

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. Il planner sceglie l'indice?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Ignora le query fuori dal filtro?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Salta le query in cui il filtro manca del tutto?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

La query 1 dovrebbe usare idx_jobs_runnable. Le query 2 e 3 dovrebbero ripiegare su una scansione (o su un altro indice, se ne hai uno). Se il planner sceglie l'indice parziale per una query in cui non te lo aspettavi, rileggi il filtro: potrebbe essere più ampio di quanto pensi.

Quando non usarlo

Gli indici parziali sono uno strumento affilato. Motivi per lasciar perdere:

• Il filtro corrisponde a gran parte della tabella. Se "attivo" vale per il 90% delle righe, un indice parziale è solo un indice normale con qualche passaggio in più. Indicizza semplicemente la colonna.
• Le tue query non includono il filtro alla lettera. Se il tuo codice usa un ORM che costruisce WHERE status IN (?, ?, ?) o calcola il filtro in modo dinamico, spesso il planner non riconosce la corrispondenza. Verifica con EXPLAIN QUERY PLAN, non darlo per scontato.
• Il sottoinsieme caldo cambia nel tempo. Un indice parziale sugli "ordini degli ultimi 30 giorni" sembra allettante ma non si può esprimere: il filtro deve essere deterministico. Dovresti ricostruire l'indice o scegliere uno schema diverso (una tabella recent_orders separata o un booleano archived da aggiornare ogni notte).

Quando il filtro è stabile e corrisponde a una piccola porzione di una tabella grande, gli indici parziali sono tra le ottimizzazioni più redditizie che puoi fare in SQLite.

Prossimo passo: leggere i piani di query

Gran parte di questa pagina si è appoggiata a EXPLAIN QUERY PLAN per confermare che un indice venisse davvero usato. Questo strumento merita una pagina tutta sua: come leggerne l'output, cosa significano le parole chiave e come distinguere una bella ricerca su indice da una scansione completa nascosta. È il prossimo argomento.