In quali casi vengono utilizzati gli indici nei database?

Gli indici nei database vengono utilizzati per ottimizzare la velocità di esecuzione delle query, in particolare:

• Ricerca dati: Accelerazione delle operazioni SELECT, specialmente con condizioni WHERE, JOIN e ORDER BY. L'indice permette al DBMS di trovare più rapidamente le righe necessarie senza scansionare l'intera tabella.
• Ordinamento: Accelerazione dell'ordinamento tramite ORDER BY. Se le colonne di ordinamento sono indicizzate, il DBMS può usarle, evitando operazioni di ordinamento aggiuntive.
• Join tra tabelle: Accelerazione delle operazioni JOIN su colonne indicizzate. L'indice aiuta a trovare rapidamente le righe corrispondenti nelle tabelle collegate.
• Unicità dei dati: Garanzia di unicità dei valori in una colonna (tramite indice unico).
• Chiavi primarie: La chiave primaria di default è un indice unico e viene usata per accedere rapidamente alla riga tramite un identificatore univoco.
• Chiavi secondarie: Utilizzate per garantire l'integrità referenziale e accelerare le operazioni JOIN tra tabelle collegate.

Esempi di colonne da indicizzare:

• Colonne frequentemente cercate (WHERE uid = 123).
• Colonne usate in condizioni JOIN (table1.id = table2.ref_id).
• Colonne frequentemente ordinate (ORDER BY created_at DESC).
• Colonne con alta cardinalità (molti valori unici).

Casi in cui gli indici possono essere inefficienti o non necessari:

• Tabelle molto piccole: In tabelle piccole, la scansione completa può essere più veloce o uguale in velocità alla ricerca tramite indice.
• Colonne con bassa cardinalità: Un indice su una colonna con pochi valori unici (ad esempio, un campo booleano TRUE/FALSE) potrebbe non portare un miglioramento significativo.
• Operazioni frequenti di scrittura (INSERT, UPDATE, DELETE): Gli indici richiedono aggiornamenti durante le modifiche dei dati, rallentando le operazioni di scrittura. Non è consigliabile indicizzare tutte le colonne in modo indiscriminato.
• Colonne in condizioni WHERE con funzioni: Se si applica una funzione alla colonna in WHERE (ad esempio, WHERE YEAR(date_column) = 2023), l'indice normale su date_column non viene utilizzato. È necessario un indice funzionale (se supportato dal DBMS) o riscrivere la query.