MySQL CREATE INDEX: Crear o añadir índices en tablas

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:

• DROP TABLE
• CREATE TRIGGER
• INSERT
• DELETE
• CREATE VIEW

Por contra, si quisiéramos eliminar un índice os recomiendo leer este otro post:

• MySQL DROP INDEX: Eliminar índices de las tablas

¿Qué son los índices de las tablas?

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.

¿Qué tipo de índices hay?

Podemos tener los siguientes tipos de índices en una tabla de MySQL:

• Únicos
• Primarios
• Ordinarios
• De texto completo
• Parte de campos o columnas

Sintaxis de la sentencia de MySQL CREATE INDEX

Vayamos con la sintaxis básica extraída de su web oficial:

Tras ver la sintaxis oficial os pongo unos ejemplos:

Con el código superior CREATE INDEX estaríamos creando uno o varios índices ordinarios en una tabla existente.

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.

Íncide único (UNIQUE, ADD UNIQUE)

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:

Explico el código superior que deberíamos usar cuando la tabla ya existe:

• Con ALTER TABLE modificamos la tabla.
• Con ADD UNIQUE añadimos opcionalmente el nombre del índice y especificamos obligatoriamente la columna de la tabla.

Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:

Os explico el código:

• CREATE TABLE creamos la tabla y definimos los tipos de columna.
• Con UNIQUE añadimos opcionalmente el nombre del índice y especificamos obligatoriamente la columna de la tabla.

Índice Primario (PRIMARY KEY)

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:

Explico el código superior que deberíamos usar cuando la tabla ya existe:

• Con ALTER TABLE modificamos la tabla.
• Con ADD PRIMARY KEY definimos la columna que hará de primaria.

Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:

Os explico el código:

• CREATE TABLE creamos la tabla y definimos los tipos de columna.
• Con PRIMARY KEY definimos la columna que hará de primaria.

Índice Ordinario (ADD INDEX)

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:

Explico el código superior que deberíamos usar cuando la tabla ya existe:

• Con ALTER TABLE modificamos la tabla.
• Con ADD INDEX definimos la columna que hará de índice ordinario. Podemos definir varias columnas.

Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:

Os explico el código:

• CREATE TABLE creamos la tabla y definimos los tipos de columna.
• Con INDEX definimos la columna o columnas que harán de índices.

Índices de texto completo (FULLTEXT)

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:

Explico el código superior que deberíamos usar cuando la tabla ya existe:

• Con ALTER TABLE modificamos la tabla.
• Con ADD FULLTEXT definimos la columna que hará de índice. Podemos definir varias columnas.

Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:

Os explico el código:

• CREATE TABLE creamos la tabla y definimos los tipos de columna.
• Con FULLTEXT definimos la columna o columnas que harán de índices.

Índices con parte de campos o columnas

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:

Explico el código superior que deberíamos usar cuando la tabla ya existe:

• Con ALTER TABLE modificamos la tabla.
• Con ADD INDEX definimos la columna que hará de índice siendo X el número de caracteres a indexar. Podemos definir varias columnas.

Ahora os muestro el código necesario para añadir el índice cuando se crea la tabla:

Os explico el código:

• CREATE TABLE creamos la tabla y definimos los tipos de columna.
• Con INDEX definimos la columna o columnas que harán de índices siendo X el número de caracteres a indexar.