CREATE INDEX

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)

Sintaxis de index_col_name:

    col_name [(length)] [ASC | DESC]

En MySQL 3.22 o posteriores, CREATE INDEX se interpreta como una sentencia ALTER TABLE para crear índices. La sentencia CREATE INDEX no hace nada antes de MySQL 3.22.

Normalmente, todos los índices de una tabla se crean al mismo tiempo que se crea la propia tabla con CREATE TABLE. CREATE INDEX permite añadir índices a tablas existentes.

Una lista de columnas de la forma (col1,col2,...) crea un índice multi-columna. Los valores de índice se forman por la concatenación de valores de las columnas dadas.

Para columnas CHAR y VARCHAR, los índices pueden ser creados para usar sólo parte de una columna, usando la sintaxiscol_name(longitud) para indexar un prefijo que contiene los primeros 'longitud' caracteres de cada valor de columna. Las columnas BLOB y TEXT también pueden ser indexada, pero se debe proporcionar una longitud de prefijo.

La sentencia mostrada a continuación crea un índice usando los 10 primeros caracteres de la columna 'name':

CREATE INDEX part_of_name ON customer (name(10));

Ya que la mayoría de los nombres normalmente se diferencian en los 10 primeros caracters, el índice no debería ser mucho más lento que un índice creado con la columna 'name' completa. Además, usando una parte de la columna para los índices puede hacer el fichero de índice mucho más pequeño, con lo que se ahorra mucho espacio de disco y se mejora la velocidad en operaciones de inserción.

Los prefijos pueden tener hasta 255 bytes de longitud (o 1000 bytes para tablas MyISAM y InnoDB a partir de MySQL 4.1.2). Notar que estos límites de prefijos vienen dados en bytes, aunque la longitud del prefijo en sentencias CREATE INDEX se interpreta como número de caracteres. Hay que tener esto en cuenta cuando se especifica una longitud de prefijo para una columna que usa un conjunto de caracteres multi-byte.

Sólo se puede añadir un índice en una columna que pueda contener valores NULL si se está usando la versión 3.23.2 o posterior de MySQL y si se usa una tabla de tipo MyISAM, InnoDB o BDB. Sólo se puede añadir un índice a una columna de tipo BLOB o TEXT si se está usando la versión 3.23.2 de MySQL o posterior y una tabla de tipo MyISAM o BDB, o MySQL 4.0.14 o posterior y una tabla de tipo InnoDB.

Una especificación de index_col_name puede terminar con ASC o DESC. Estas palabras clave están permitidas para extensiones futuras para especificar el almacenamiento de valores de índices ascendentes o descendentes. Actualmente se verifica su sintaxis, pero se ignoran; los valores de índices siempre se almacenan en orden ascendente.

A partir de MySQL 4.1.0, algunos motores de almacenamiento permiten especificar un tipo de índice cuando este es creado. La sintaxis para el especificador index_type es USING type_name. Los valores posibles de type_name soportados por los diferentes motores de almacenamiento se muestran en la siguiente tabla. Cuando se listan varios tipos de índices, el primero es el valor por defecto cuando no se especifica uno.

Ejemplo:

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);

Se puede usar TYPE type_name como un sinónimo de USING type_name para especificar un tipo de índice. Sin embargo, USING es el formato preferible. Además, el nombre de índice que precede al tipo de índice es la sintaxis de especificación de índice no es opcional con TYPE. Esto es así porque, al contrario que USING, TYPE no es una palabra reservada y puede ser interpretada como un nombre de índice.

Si se especifica un tipo de índice que no es legal para el motor de almacenamiento, pero existe otro tipo de índice disponible que el motor puede usar sin que afecte a los resultados de consultas, el motor podrá usar el tipo disponible.

Los índices FULLTEXT sólo pueden indexar columnas CHAR, VARCHAR y TEXT, y sólo en tablas MyISAM. Los índices FULLTEXT están disponibles a partir de MySQL 3.23.23.

Los índices SPATIAL sólo pueden indexar columnas spatial, y sólo en tablas MyISAM. Los índices SPATIAL están disponibles a partir de MySQL 4.1.