Indici in SQLite: CREATE INDEX, quando usarli e perché

Cos'è davvero un indice

Un indice è una struttura dati separata, un B-tree ordinato, che permette a SQLite di trovare le righe in base al valore di una colonna senza scansionare tutta la tabella. Senza indice, una query come WHERE email = 'rosa@example.com' legge ogni riga e le controlla una per una. Con un indice su email, SQLite percorre l'albero in circa log(n) passi e salta direttamente alla corrispondenza.

Questa velocità non è gratis. L'indice è una copia della colonna indicizzata più un puntatore alla riga. Ogni INSERT, ogni UPDATE di una colonna indicizzata e ogni DELETE devono aggiornare anche l'indice. Lo spazio su disco aumenta. La velocità di scrittura cala un po'. Il patto è: paghi sulle scritture, risparmi molto di più sulle letture.

Creare un indice

La sintassi di base:

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

Convenzione sui nomi: la maggior parte dei team usa idx_<table>_<column>, così è evidente a cosa serve l'indice. Il nome deve essere unico in tutto il database, non solo nella tabella: per questo contiene il nome della tabella.

Per rimuoverne uno:

DROP INDEX idx_users_email;

Gli indici sono pura impalcatura per le prestazioni. Eliminarne uno non tocca mai i tuoi dati, cambia solo la velocità delle query.

Indici univoci

Un indice univoco fa due cose: velocizza le ricerche e impone che nessuna coppia di righe abbia lo stesso valore indicizzato.

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

Il terzo inserimento fallisce con UNIQUE constraint failed: accounts.username. SQLite crea già in automatico indici univoci per le colonne PRIMARY KEY e UNIQUE: li vedrai con nomi come sqlite_autoindex_<table>_<n>. Devi scrivere CREATE UNIQUE INDEX solo quando il vincolo non è stato dichiarato sulla tabella stessa.

Cosa fa davvero il planner

Aggiungere un indice non garantisce che SQLite lo usi. Il query planner sceglie una strategia per ogni query, e puoi vedere cosa ha scelto con EXPLAIN QUERY PLAN:

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;

Cerca SEARCH ... USING INDEX idx_orders_customer nell'output: significa che l'indice viene usato. Se vedi SCAN orders, il planner ha deciso che una scansione completa costava meno (spesso a ragione sulle tabelle minuscole) oppure la forma della tua query gli ha impedito di usare l'indice. Più avanti c'è un'intera pagina su come leggere questi piani.

Quando un indice non viene usato

Gli indici hanno alcuni punti ciechi ben noti. Ognuno di questi rende inutile l'indice su email:

-- Una funzione avvolge la colonna
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Carattere jolly iniziale in LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Un tipo diverso forza una conversione
SELECT * FROM users WHERE email = 12345;

Il B-tree è ordinato sul valore grezzo di email, quindi qualsiasi cosa trasformi la colonna al momento della query costringe a una scansione. Le soluzioni variano: salvare i dati già normalizzati (una colonna email_lower), usare un indice su espressione (CREATE INDEX idx ON users(lower(email))) oppure la ricerca full-text di SQLite per le ricerche di sottostringhe.

Indici di copertura

Se un indice contiene tutte le colonne di cui la query ha bisogno, SQLite può rispondere senza mai toccare la tabella: è un indice di copertura (covering index). Il trucco è includere colonne extra nella definizione dell'indice:

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;

Dato che entrambe le colonne richieste dalla query stanno nell'indice, SQLite riporta USING COVERING INDEX. Non serve leggere la riga. Gli indici di copertura sono tra le ottimizzazioni più efficaci per i percorsi di lettura più frequenti; il prezzo è un indice più grande. Gli indici su più colonne sono un argomento a sé: la prossima pagina li tratta come si deve.

Elencare e ispezionare gli indici

Due modi per vedere cosa c'è:

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

Così ottieni tutti gli indici del database con la loro istruzione CREATE. Per una sola tabella, PRAGMA index_list('products'); mostra solo gli indici di quella tabella, e PRAGMA index_info('idx_products_name'); mostra quali colonne indicizza ciascuno. Tutto ciò che inizia con sqlite_autoindex_ è stato creato automaticamente per un vincolo PRIMARY KEY o UNIQUE: quelli non puoi eliminarli.

Quando non aggiungere un indice

Alcune situazioni in cui un indice peggiora le cose:

• Tabelle minuscole. Qualche centinaio di righe si scansiona in microsecondi. Il planner probabilmente ignorerà comunque l'indice, e tu avrai aggiunto un costo in scrittura per niente.
• Colonne scritte spesso e interrogate di rado. Ogni scrittura aggiorna ogni indice. Indicizzare una colonna su cui non filtri quasi mai è un costo puro.
• Colonne con pochi valori distinti, da sole. Un indice su una colonna status con tre valori possibili non restringe molto. Può comunque aiutare come seconda colonna di un indice composto, o come indice parziale, ma da solo spesso non vale la pena.
• Già coperte. Se hai un indice su (a, b), non te ne serve anche uno su (a). SQLite usa le colonne iniziali di un indice composto per le query che filtrano solo su a.

La risposta onesta a "dovrei aggiungere questo indice?" è quasi sempre: provalo, esegui EXPLAIN QUERY PLAN, misura con dati realistici, decidi.

Prossimo passo: gli indici composti

Un indice su una sola colonna copre molti casi, ma le query reali spesso filtrano e ordinano su più colonne insieme. Gli indici composti, cioè indici su (a, b, c), gestiscono questi casi, e l'ordine delle colonne conta più di quanto si pensi. È il tema della prossima pagina.