Sentencia MySQL CREATE INDEX

La sentencia `CREATE INDEX` de MySQL se utiliza para crear un índice en la(s) columna(s) de una tabla para mejorar la velocidad de recuperación de las consultas. Los índices son fundamentales para optimizar el rendimiento de las bases de datos, sobre todo cuando se trata de grandes conjuntos de datos.

Utilización

La sentencia `CREAR ÍNDICE` se utiliza cuando necesitas mejorar el rendimiento de las operaciones de recuperación de datos. Es especialmente útil para acelerar las consultas `SELECT` que filtran u ordenan datos.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

En esta sintaxis, `CREAR ÍNDICE` define el nombre del índice y especifica la tabla y las columnas sobre las que se crea el índice.

Ejemplos

1. Creación de índices básicos

CREATE INDEX idx_lastname
ON employees (last_name);

Este ejemplo crea un índice llamado `idx_apellido` en la columna `apellido` de la tabla `empleados`, optimizando las consultas que filtran por apellidos.

2. Índice compuesto

CREATE INDEX idx_name_dob
ON employees (last_name, birth_date);

Esta sintaxis demuestra la creación de un índice compuesto sobre "apellido" y "fecha_nacimiento", útil para consultas que filtran por ambas columnas.

3. Índice único

CREATE UNIQUE INDEX idx_email
ON users (email);

Este ejemplo crea un índice único en la columna `email` de la tabla `users` para garantizar que todas las entradas de correo electrónico sean únicas y acelerar las búsquedas por correo electrónico.

4. Índice de texto completo

CREATE FULLTEXT INDEX idx_content
ON articles (content);

Este ejemplo crea un índice `FULLTEXT` en la columna `content` de la tabla `articles`, útil para las búsquedas de texto completo.

5. Índice Espacial

CREATE SPATIAL INDEX idx_location
ON locations (geo_point);

Esto crea un índice `SPATIAL` en la columna `geo_point` de la tabla `locations`, beneficioso para los tipos de datos geográficos.

Consejos y buenas prácticas

• Indexa selectivamente. Crea índices sólo en las columnas en las que se realicen búsquedas u ordenaciones frecuentes, para evitar sobrecargas innecesarias.
• Controla el rendimiento. Utiliza la sentencia `EXPLAIN` para evaluar el rendimiento de la consulta y determinar si un índice es beneficioso.
• Considera el tipo de índice. Elige el tipo de índice adecuado (por ejemplo, único, de texto completo, espacial) en función de tus datos y necesidades de consulta.
• Equilibrio entre lectura y escritura. Ten en cuenta que, aunque los índices aceleran las lecturas, pueden ralentizar las operaciones de escritura (INSERTAR, ACTUALIZAR, ELIMINAR).
• Funcionamiento interno. La mayoría de los índices utilizan estructuras de árbol B para mantener los datos ordenados, facilitando las búsquedas rápidas.
• Limitaciones. Comprende las limitaciones, como el número máximo de índices por tabla o el tamaño máximo de las claves de índice.
• Convenciones de denominación. Utiliza nombres claros y descriptivos para los índices a fin de mantener la legibilidad, como anteponer el prefijo `idx_`.
• Manejabilidad. Revisa y gestiona periódicamente los índices utilizando `ALTER TABLE` para modificarlos y `DROP INDEX` para eliminar los índices innecesarios.

Gestionar y eliminar índices

Para modificar un índice, puedes utilizar la sentencia `ALTER TABLE`. Para eliminar un índice, se utiliza la sentencia `DROP INDEX`, como se muestra a continuación:

DROP INDEX idx_lastname ON employees;

Consideraciones

• Clave primaria vs. Índice único. Una clave primaria es una restricción que garantiza valores únicos y la no anulabilidad, mientras que un índice único sólo garantiza la unicidad.
• Columnas de baja cardinalidad. Evita indexar columnas con baja cardinalidad, ya que puede que no aporten ventajas de rendimiento significativas.
• Mesas pequeñas. Los índices en tablas muy pequeñas pueden no producir mejoras notables de rendimiento debido a la sobrecarga de mantener el índice.