Post para repasar la sentencia de MySQL CREATE INDEX que sirve para crear o añadir índices en las tablas de una base de datos y optimizar su rendimiento.
Otras sentencias MySQL interesantes son:
Por contra, si quisiéramos eliminar un índice os recomiendo leer este otro post:
Los índices de las tablas ayudan a indexar el contenido de diversas columnas para facilitar la búsquedas de contenido de cuando se ejecutan consultas sobre esas tablas.
De ahí que la creación de índices optimiza el rendiemiento de las consultas y a su vez el de la BBDD.
Podemos tener los siguientes tipos de índices en una tabla de MySQL:
Vayamos con la sintaxis básica extraída de su web oficial:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] ...
Tras ver la sintaxis oficial os pongo unos ejemplos:
CREATE INDEX nombre_indice ON nombrede_tabla(columna [columna2...]);
Con el código superior CREATE INDEX estaríamos creando uno o varios índices ordinarios en una tabla existente.
CREATE FULLTEXT INDEX nombre_indice ON nombre_tabla(columna [columna2...]);
Con este otro código CREATE INDEX estaríamos creando uno o varios índices de texto completo FULLTEXT en una tabla existente.
Yo personalmente prefiero definir lo íncides al crear la tabla o si la tabla ya existe uso la sentencia ALTER TABLE para modificarla.
Un índice único no permite la inserción los valores duplicados en dicha columna que marquemos como único. S puede aplicar a varias columnas. Os pongo 2 ejemplos para añadir un índice único a una tabla:
ALTER TABLE nombre_de_la_tabla ADD UNIQUE [nombre_indice] (nombre_columna, [bombre_columna2]...);
Explico el código superior que deberíamos usar cuando la tabla ya existe:
Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:
CREATE TABLE nombre_de_la_tabla (nombre_columna1, nombre columna 2...) UNIQUE [nombre_indice] (nombre_columna)
Os explico el código:
Un índice primario es también conocido como Primary KEY, permite crear un índice en cuyo valor es único y no puede ser NULL. Solo puede haber un índice primario por tabla.
Os pongo 2 ejemplos para añadir una PRIMARY KEY a una tabla:
ALTER TABLE nombre_de_la_tabla ADD PRIMARY KEY (nombre_columna);
Explico el código superior que deberíamos usar cuando la tabla ya existe:
Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:
CREATE TABLE nombre_de_la_tabla (nombre_columna1, nombre columna 2...) PRIMARY KEY (nombre_columna)
Os explico el código:
Este tipo de índice en MySQL permite le existencia o inserción de valores duplicados en una tabla. Con este tipo solamente mejoramos la ejecución y rendimiento de las consultas (Que no es poco). Os pongo 2 ejemplos para añadir un índice ordinario a una tabla:
ALTER TABLE nombre_de_la_tabla ADD INDEX [nombre_indice] (nombre_columna, [bombre_columna2]...);
Explico el código superior que deberíamos usar cuando la tabla ya existe:
Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:
CREATE TABLE nombre_de_la_tabla (nombre_columna1, nombre columna 2...) INDEX (nombre_columna, [bombre_columna2]...)
Os explico el código:
Estos índices FULLTEXT o de texto completo se usan en tablas del tipo MyISAM, se debe relacionar con columnas de tipo TEXT, CHAR o VARCHAR.
Optimizan el rendieminto de las consultas con búsquedas en tablas con gran información de campos de texto. Os pongo 2 ejemplos para añadir un índice ordinario a una tabla:
ALTER TABLE nombre_de_la_tabla ADD FULLTEXT [nombre_indice] (nombre_columna, [bombre_columna2]...);
Explico el código superior que deberíamos usar cuando la tabla ya existe:
Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:
CREATE TABLE nombre_de_la_tabla (nombre_columna1, nombre columna 2...) FULLTEXT (nombre_columna, [bombre_columna2]...)
Os explico el código:
Similar al índice anterior FULLTEXT pero solamente cogiendo parte del contenido de la columna. Este tipo de índices aligeran las consultas sobre campos de textos. Por ejemplo si realizamos búsquedas sobre tablas de nombres o apellidos con seleccionar los primeros caracteres nos valdría para realizar la comparación.
Estos índices se definen seleccionando los primeros X caracteres de la columna. Os pongo 2 ejemplos para añadir un índice con parte de una columna:
ALTER TABLE nombre_de_la_tabla ADD INDEX (nombre_columna(X), [bombre_columna2(X)]...);
Explico el código superior que deberíamos usar cuando la tabla ya existe:
Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:
CREATE TABLE nombre_de_la_tabla (nombre_columna1, nombre columna 2...) INDEX (nombre_columna(X), [bombre_columna2(X)]...)
Os explico el código: