Guida SQLite interattiva: Indici compositi

Un indice, più colonne

Un indice composito, a volte chiamato indice multicolonna, è un unico indice costruito su due o più colonne. Lo crei elencando le colonne in ordine:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    total REAL NOT NULL
);

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

L'indice idx_orders_customer_status salva le voci ordinate prima per customer_id, poi per status all'interno di ogni cliente. Tutto sta in questo ordinamento: ogni altra cosa sugli indici compositi deriva da qui.

Il modello mentale: un elenco telefonico ordinato

Pensa a un vecchio elenco telefonico. Le voci sono ordinate per cognome e, a parità di cognome, per nome. È esattamente l'aspetto di un indice su (last_name, first_name).

Alcune ricerche costano poco, altre no:

"Trova tutti quelli che si chiamano Patel": facile, i Patel stanno tutti vicini.
"Trova Priya Patel": facile, salti ai Patel, poi scorri fino a Priya.
"Trova tutte le persone di nome Priya": lento, devi scorrere ogni pagina. Le Priya sono sparse tra tutti i cognomi.

Un indice composito di SQLite funziona allo stesso modo. La prima colonna è la chiave di ordinamento principale; la seconda ordina solo le voci che condividono lo stesso valore nella prima colonna.

La regola del prefisso più a sinistra

SQLite può usare un indice composito per una query solo quando la clausola WHERE vincola un prefisso sinistro delle sue colonne. Per un indice su (a, b, c):

Filtro su a: usa l'indice.
Filtro su a e b: usa l'indice.
Filtro su a, b e c: usa l'indice.
Filtro solo su b, solo su c, oppure su b e c: l'indice non viene usato.

Puoi verificarlo direttamente con EXPLAIN QUERY PLAN:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

Il primo piano riporta SEARCH events USING INDEX idx_events_user_kind_time. Il secondo ripiega su SCAN events: filtrare solo su kind salta la colonna iniziale user_id, quindi l'indice è inutile per quella query.

L'ordine delle colonne è una scelta di progetto

Visto che il prefisso sinistro conta, l'ordine in cui elenchi le colonne in CREATE INDEX è una scelta vera, non una questione di stile. Due regole pratiche:

Metti per prima la colonna su cui filtri più spesso. È quella che rende l'indice utile per la gamma più ampia di query.
Metti le colonne di uguaglianza prima di quelle di intervallo. SQLite può scendere nell'indice usando =, poi scorrere un intervallo contiguo usando <, > o BETWEEN, ma solo sull'ultima colonna utilizzata.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- Bene: uguaglianza su region prima, intervallo su sold_at dopo.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

Il piano mostra SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite salta direttamente a region = 'EU', poi avanza lungo l'intervallo di date. Inverti l'ordine delle colonne in (sold_at, region) e la stessa query deve scorrere tutte le righe dell'intervallo di date e ricontrollare region per ciascuna.

Composito contro diversi indici su singole colonne

Una domanda frequente: meglio creare un indice su (a, b) o due indici separati su a e b?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- Opzione A: un indice composito.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Opzione B (alternativa): due indici su singole colonne.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

Per il filtro combinato l'indice composito è più veloce: SQLite va dritto alle voci (project_id, state) corrispondenti. Con due indici su singole colonne, SQLite di solito ne sceglie uno, lo usa per restringere le righe, poi ricontrolla l'altra colonna su ogni riga trovata. A volte riesce a intersecarli, ma l'indice composito è la risposta più pulita quando le colonne vengono interrogate insieme.

Se project_id e state vengono interrogate anche separatamente, potresti volerli entrambi: il composito per il filtro combinato, più un indice su state per le query che filtrano solo su quella colonna.

Covering index

Quando un indice include tutte le colonne di cui una query ha bisogno, sia quelle di filtro sia quelle selezionate, SQLite può rispondere alla query senza toccare affatto la tabella. È un covering index, e una query non può essere più veloce di così.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'first'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'rush');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

Il piano mostra USING COVERING INDEX idx_invoices_cover. La query legge issued_at e total direttamente dall'indice: notes e id non servono, quindi la tabella non viene mai aperta. Aggiungere una colonna a un indice composito solo per coprire una query frequente è uno scambio che conviene quando quella query viene eseguita di continuo.

Vincoli UNIQUE compositi

Gli indici compositi impongono anche l'unicità su combinazioni di colonne. Utile quando nessuna colonna è unica da sola, ma la combinazione deve esserlo:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- ok, corso diverso
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- fallisce: la coppia si ripete

Il terzo insert solleva UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. La stessa coppia esiste già nell'indice, quindi SQLite rifiuta il duplicato.

Insidie da conoscere

Un OR tra colonne non iniziali blocca l'indice. WHERE a = 1 OR b = 2 su un indice (a, b) di solito non può usare affatto l'indice: SQLite deve considerare i due rami separatamente.
Le funzioni sulle colonne indicizzate disattivano l'indice. WHERE lower(email) = 'x' non userà un indice su email. Indicizza l'espressione, oppure normalizza i dati in fase di insert.
Gli indici non sono gratis. Ogni indice viene aggiornato a ogni INSERT, UPDATE (delle colonne indicizzate) e DELETE. Tre indici compositi su una tabella con molte scritture possono dominare il costo delle scritture.
Esegui ANALYZE dopo aver creato gli indici. Il planner di SQLite usa le statistiche raccolte da ANALYZE per scegliere tra gli indici candidati. Senza quelle statistiche ripiega su euristiche non sempre ottimali.

Un flusso di lavoro pratico

Quando ottimizzi una query lenta, il ciclo di solito è questo:

Esegui EXPLAIN QUERY PLAN sulla query per vedere cosa fa SQLite oggi.
Se sta scansionando, guarda la clausola WHERE: qual è la colonna di uguaglianza? Qual è quella di intervallo? Cosa viene selezionato?
Crea un indice composito ordinato prima per uguaglianza e poi per intervallo, aggiungendo in fondo le colonne selezionate se un covering index aiuta.
Esegui ANALYZE.
Esegui di nuovo EXPLAIN QUERY PLAN. Conferma che il piano è cambiato e che l'indice viene usato.
Misura il tempo della query prima e dopo, su dati rappresentativi.

Salta il passo 6 a tuo rischio. Un indice che nel piano sembra corretto può comunque essere più lento nella pratica se la tabella è piccola o se il planner sceglie un altro percorso.

Prossimo passo: indici parziali

Gli indici compositi coprono tutte le righe della tabella. Ma spesso conta solo un piccolo sottoinsieme di righe: ticket aperti, job non ancora elaborati, record non eliminati. Un indice parziale ti permette di indicizzare solo quelle righe, con una clausola WHERE incorporata nell'indice stesso. È l'argomento della prossima pagina.

Domande frequenti

Cos'è un indice composito in SQLite?

Un indice composito è un unico indice che copre due o più colonne. Lo crei con CREATE INDEX idx_name ON table(col_a, col_b). SQLite salva le voci ordinate prima per col_a, poi per col_b all'interno di ogni valore di col_a: come un elenco telefonico ordinato per cognome e poi per nome.

L'ordine delle colonne conta in un indice composito SQLite?

Sì, moltissimo. SQLite può usare un indice composito per una query solo se la clausola WHERE filtra su un prefisso sinistro delle colonne indicizzate. Un indice su (a, b, c) aiuta le query che filtrano su a, su a e b, o su tutte e tre, ma non una query che filtra solo su b o solo su c.

Quando conviene un indice composito invece di indici separati su singole colonne?

Usa un indice composito quando le query filtrano od ordinano regolarmente sulla stessa combinazione di colonne. Gli indici separati su singole colonne funzionano quando ogni colonna viene interrogata in modo indipendente. Esegui EXPLAIN QUERY PLAN per vedere quale indice sceglie davvero SQLite: è l'unico riscontro affidabile.

Cos'è un covering index in SQLite?

Un covering index include tutte le colonne di cui la query ha bisogno, così SQLite può rispondere direttamente dall'indice senza toccare la tabella. EXPLAIN QUERY PLAN mostra USING COVERING INDEX quando succede. Aggiungere colonne extra a un indice composito solo per coprire una query molto frequente è un'ottimizzazione comune.

Concetti correlati