Creare un indice

Una sola semplice istruzione per creare un indice in MySQL

Per creare un indice, se non è la chiave primaria, esiste una semplice istruzione CREATE INDEX, come alternativa ad ALTER TABLE.

CREATE INDEX nome_indice ON nome_tabella (lista_campi);

Il nome dell’indice non è molto importante, tranne per il fatto che viene utilizzato in alcuni messaggi di errore.

nome_tabella è il nome della tabella sulla quale si sta creando l’indice e lista_campi è il campo, o l’elenco dei campi separati da una virgola, che devono essere indicizzati.

E’ possibile indicizzare solo la prima parte di un campo, cioè il prefisso. Ad esempio, si può creare un indice sui primi 5 caratteri di un campo CHAR:

CREATE INDEX icognome ON clienti (cognome(5));

Il prefisso può essere utilizzato su campi di tipo CHAR, VARCHAR, BINARY, e VARBINARY, mentre deve essere usato per forza se si vuole indicizzare campi TEXT e BLOB, che sono di dimensioni più grandi. Per i campi CHAR, VARCHAR e TEXT il numero tra parentesi indica quanti caratteri devono essere indicizzati, mentre per i campi BINARY, VARBINARY e BLOB l’unità di misura sono i byte.

L’uso di un prefisso può rendere l’indice molto più piccolo in termini di spazio su disco. Se la parte indicizzata è differenze per quasi tutti i record (esempio: i primi 5 caratteri del cognome), l’uso del prefisso dovrebbe rallentare le ricerche in maniera irrisoria.

L’indice può anche essere unico, nel qual caso non saranno più accettati valori ripetuti nei campi indicizzati (eccetto naturalmente il caso del non-valore NULL). Un indice unico è molto più performante.

CREATE UNIQUE INDEX nominativo ON clienti (nome, cognome);

Per creare un indice FULLTEXT (solo tabelle MyISAM / Maria):

CREATE FULLTEXT INDEX nome_indice ON nome_tabella (lista_campi);

Se si indicizza un campo di tipo geometrico, è possibile (ma non obbligatorio) creare un indice spaziale (solo tabelle MyISAM / Maria e non ammette valori NULL):

CREATE SPATIAL INDEX nome_indice ON nome_tabella (lista_campi);

Indine è possibile specificare quale struttura di dati debba essere usata per l’indice. Al momento MySQL supporta tre tipi di indice: HASH, BTREE e RTREE. Ogni storage engine può supportare uno o più tipi, perciò occorre accertarsi che il tipo di indice desiderato sia supportato dallo storage engine che si sta usando. BTREE è generalmente più performante di HASH. RTREE è ammesso solo per gli indici spaziali, quindi solo nelle tabelle MyISAM.

MyISAM e Maria supportano BTREE e RTREE, InnoDB supporta solo BTREE, MEMORY supporta HASH e BTREE.

Per specificare la struttura di dati da usare:

CREATE INDEX nome_indice ON nome_tabella (lista_campi) USING tipo;

Argomenti chiave:

• create index
• creare indici
• indici