Indici MySQL e PostgreSQL — Come Funzionano e Quando Crearli per Performance Reali

Il tuo database rallenta. Una query che dovrebbe impiegare millisecondi ti tiene in attesa secondi, a volte minuti. Il cliente ti scrive che il gestionale è lentissimo. Il magazzino non risponde. E tu, sviluppatore o system administrator, sai che il problema è spesso sugli indici — o sulla loro assenza.

Noi, di Meteora Web, abbiamo visto decine di progetti bloccarsi per questo. Un indice sbagliato fa danni quanto uno mancante. E le PMI italiane, con budget limitati, non possono permettersi server che compensano query scritte male. Lavoriamo con database da anni — MySQL e PostgreSQL sono i nostri cavalli di battaglia insieme a Laravel e WooCommerce — e sappiamo che un buon indice è la differenza tra un sito che vende e un sito che butta via clienti.

In questa guida ti spieghiamo come funzionano gli indici, quando crearli e quando evitarli, con esempi pratici che puoi testare subito. Niente teoria astratta: solo quello che serve per far girare il database come deve.

Come funzionano gli indici in MySQL e PostgreSQL?

Un indice è come l'indice analitico di un libro: invece di sfogliare tutte le pagine per trovare una parola, vai direttamente alla pagina giusta. Tecnicamente, è una struttura dati separata che memorizza una copia ordinata di una o più colonne, con un puntatore alla riga originale. Le strutture più comuni sono B-tree (bilanciato, usato da entrambi i DBMS) e Hash (solo per uguaglianze, supportato da PostgreSQL con l'indice hash).

Quando esegui una query del tipo SELECT * FROM ordini WHERE cliente_id = 42 e non c'è un indice su cliente_id, il database fa una scansione sequenziale — esamina ogni riga della tabella. Con un indice, invece, salta direttamente ai record corrispondenti.

La struttura B-tree in dettaglio

Il B-tree mantiene i dati ordinati e bilancia l'albero in modo che ogni ricerca richieda un numero logaritmico di accessi. È il tipo predefinito sia in MySQL che in PostgreSQL. Supporta:

• Ricerca per uguaglianza: WHERE colonna = valore
• Ricerca per intervallo: WHERE colonna BETWEEN a AND b
• Ordinamento: ORDER BY colonna
• Ricerca con pattern prefisso: WHERE colonna LIKE 'abc%'

MySQL usa B-tree anche per indici UNIQUE e indici su colonne TEXT/VARCHAR con prefisso. PostgreSQL offre inoltre indici GiST (per dati geometrici o full-text), GIN (per array, JSONB), BRIN (per dati spazialmente correlati) e Hash (solo uguaglianza).

Esempio pratico: creare un indice semplice

-- MySQL e PostgreSQL
CREATE INDEX idx_ordini_cliente ON ordini (cliente_id);

-- Per verificare che venga usato (spiegato dopo)
EXPLAIN SELECT * FROM ordini WHERE cliente_id = 42;

Creare un indice è veloce, ma ha un costo: rallenta le operazioni di INSERT, UPDATE e DELETE perché ogni modifica alla tabella deve aggiornare anche l'indice. Inoltre, occupa spazio su disco. Per questo non si mettono indici su ogni colonna a caso.

Quando creare un indice e quando invece evitarlo?

La regola d'oro: crea un indice solo se risolve una query reale che il tuo sistema esegue regolarmente. Non basarti su ipotesi. Noi partiamo sempre dai log delle query lente (slow query log) o da EXPLAIN.

Segnali che indicano che serve un indice

• Query che eseguono scansioni sequenziali su tabelle grandi (migliaia di righe o più).
• Operazioni di JOIN senza indici sulle colonne chiave (nei CREATE INDEX delle foreign key spesso si dimenticano).
• Ordinamenti frequenti (ORDER BY) su colonne non indicizzate.
• Filtri su colonne con alta selettività (poche righe per valore).
• Query di ricerca su date, categorie, stati (es. WHERE stato = 'attivo').

Quando non creare un indice

• Colonne con bassissima selettività (es. sesso con solo 'M' e 'F' su tabella enorme — l'indice non aiuta, perché deve leggere metà delle righe comunque).
• Colonne che vengono quasi mai usate in WHERE, JOIN o ORDER BY.
• Tabelle piccole (es. meno di 1000 righe): l'overhead dell'indice può essere peggiore della scansione.
• Colonne con molti aggiornamenti (es. ultimo_accesso che cambia ogni minuto) — l'indice si aggiorna ogni volta, rallentando le scritture.

Indici composti: l'arma segreta

Se una query filtra su più colonne, un indice composto può essere molto più efficiente di due indici singoli. L'ordine delle colonne è cruciale: metti prima quella con la maggiore selettività.

-- Utile per query come: WHERE cliente_id = 42 AND data > '2026-01-01'
CREATE INDEX idx_ordini_cliente_data ON ordini (cliente_id, data_ordine DESC);

Ricorda: l'indice può essere usato anche per il solo prefisso sinistro. Se hai INDEX (a,b,c), funziona per query su a, a+b, a+b+c, ma non per query su b o c da soli.

Come misurare se un indice sta funzionando?

Non fidarti dell'intuito. Usa EXPLAIN per vedere se il database usa l'indice. Un esempio concreto dal nostro lavoro:

EXPLAIN SELECT * FROM ordini WHERE cliente_id = 42 AND data_ordine > '2026-03-01';
-- Output tipico: "Index Scan" o "Index Seek" = sta usando l'indice
-- "Seq Scan" o "Full Table Scan" = non lo sta usando

In PostgreSQL, l'output include il tipo di scan (Index Scan, Bitmap Heap Scan, Seq Scan), la stima delle righe e il costo. In MySQL, EXPLAIN mostra la colonna type: ref o range sono buoni segni, ALL significa scansione totale.

Strumenti pratici

• Slow Query Log: abilitalo (in MySQL SET GLOBAL slow_query_log = 1) e analizza le query che impiegano più di un secondo.
• pg_stat_user_tables (PostgreSQL): mostra scansioni sequenziali e scansioni per indice. Se vedi seq_scan alto su una tabella grande, manca un indice.
• Performance Schema di MySQL: fornisce metriche dettagliate.

Noi confrontiamo sempre il costo delle query con e senza indice. Spesso un indice riduce il tempo di risposta del 90% o più. Ma attenzione: se l'indice non viene mai usato, è solo spreco di spazio e lentezza in scrittura.

Che differenza c'è tra gli indici di MySQL e PostgreSQL?

Entrambi supportano B-tree, ma ci sono differenze importanti.

Tipi di indici

• MySQL (InnoDB): B-tree, FULLTEXT (per testo), SPATIAL (per geometrie). Non ha indici Hash tranne per le tabelle MEMORY. Gli indici cluster con la primary key implicita.
• PostgreSQL: B-tree, Hash, GiST, GIN, BRIN, SP-GiST. Molta più varietà. Ad esempio, per colonne JSONB usa GIN, per dati geometrici GiST.

Indici parziali (PostgreSQL)

PostgreSQL permette di creare indici parziali che indicizzano solo un sottoinsieme di righe. Esempio:

CREATE INDEX idx_ordini_attivi ON ordini (cliente_id) WHERE stato = 'attivo';

Questo indice è più piccolo e veloce delle query che filtrano solo ordini attivi. MySQL non ha questa funzionalità (si può simulare con viste materializzate o indici su colonne calcolate, ma non è nativo).

Indici funzionali (PostgreSQL)

Puoi indicizzare il risultato di una funzione, ad esempio:

CREATE INDEX idx_ordini_anno ON ordini (EXTRACT(YEAR FROM data_ordine));

In MySQL, per ottenere lo stesso effetto devi creare una colonna generata virtuale e indicizzarla. È più macchinoso.

Clustering degli indici

In InnoDB (MySQL), la primary key è clusterizzata: i dati sono fisicamente ordinati secondo la PK. Gli indici secondari puntano alla PK, non direttamente alla riga. In PostgreSQL, gli indici sono sempre non clusterizzati, ma puoi usare CLUSTER per riordinare la tabella secondo un indice (operazione una tantum, non viene mantenuta automaticamente).

Queste differenze contano quando progetti lo schema. Noi, di Meteora Web, scegliamo PostgreSQL per progetti complessi che richiedono indici funzionali o parziali, mentre per e-commerce su WooCommerce (MySQL/InnoDB) sfruttiamo la clusterizzazione della PK e attenzione agli indici secondari.

Come evitare gli errori più comuni con gli indici?

Abbiamo visto gli stessi errori decine di volte. Ecco i tre peggiori.

Errore 1: indicizzare colonne che vengono usate solo in output

L'indice serve solo per WHERE, JOIN, ORDER BY e GROUP BY. Se una colonna appare solo in SELECT, non serve a nulla — a meno che tu non stia facendo un covering index (tutte le colonne della query sono nell'indice, così il database legge solo l'indice senza toccare la tabella). Esempio: CREATE INDEX idx_ordini_covering ON ordini (cliente_id, data_ordine) INCLUDE (totale, stato); (PostgreSQL supporta INCLUDE, MySQL no).

Errore 2: troppi indici su una tabella

Ogni indice aggiuntivo rallenta le scritture. In una tabella che subisce molte INSERT/UPDATE (log, ordini in tempo reale), avere 10 indici può far crollare le performance di scrittura. Noi consigliamo di non superare 5-7 indici per tabella, a meno di necessità specifiche.

Errore 3: ignorare l'ordine delle colonne negli indici composti

Se l'indice è su (categoria, data) ma la query filtra solo per data, l'indice non verrà usato. Progetta gli indici basandoti sulle query reali, non su intuizioni.

Un esempio dal nostro lavoro: un cliente e-commerce aveva una tabella ordini con 200.000 righe. Aveva un indice su (data_ordine, cliente_id). Le query di ricerca per cliente erano lentissime. Abbiamo invertito l'ordine, creando (cliente_id, data_ordine). Il tempo di risposta è passato da 3 secondi a 30 millisecondi. La logica: il cliente filtra quasi sempre prima per cliente, poi per data. L'ordine delle colonne deve rispecchiare la selettività.

Cosa fare adesso

1. Identifica le query lente — abilita slow query log o usa Performance Schema / pg_stat_user_tables.
2. Analizza con EXPLAIN — cerca scansioni sequenziali su tabelle grandi.
3. Crea un indice mirato — scegli colonne usate in WHERE, JOIN, ORDER BY. Se serve più di una colonna, fai un indice composto con ordine corretto.
4. Verifica l'effetto — ripeti EXPLAIN e misura i tempi. Se l'indice non viene usato, rimuovilo.
5. Monitora le scritture — se noti rallentamenti in INSERT/UPDATE, potresti avere troppi indici. Rivedi la strategia.

Noi, di Meteora Web, mettiamo a disposizione la nostra esperienza per aiutare le PMI italiane a ottimizzare i database. Se vuoi approfondire, leggi la nostra guida completa su SQL e Database Relazionali o contattaci per un'analisi personalizzata.