Índices en MySQL: CREATE INDEX, tipos y EXPLAIN

Un índice en MySQL ayuda a encontrar filas sin revisar la tabla completa en búsquedas, joins, ordenaciones y agrupaciones. Funciona como el índice de un libro: en lugar de recorrer todas las páginas, MySQL puede ir directamente a la zona donde están los datos.

El precio de esa ventaja es doble: cada índice ocupa espacio y MySQL debe mantenerlo actualizado en cada INSERT, UPDATE y DELETE. Si todavía estás diseñando la tabla, también puedes declarar índices dentro de una sentencia CREATE TABLE en MySQL.

Índice simple con CREATE INDEX

CREATE INDEX nombre_indice
ON nombre_tabla (columna);

Ejemplo:

CREATE INDEX idx_clientes_ciudad
ON clientes (ciudad);

Este índice puede mejorar consultas como:

SELECT *
FROM clientes
WHERE ciudad = 'Madrid';

CREATE INDEX se corresponde con añadir un índice mediante ALTER TABLE ... ADD INDEX. No lo uses para crear una PRIMARY KEY: la clave primaria se define en CREATE TABLE o con ALTER TABLE.

Crear un índice único

Un índice UNIQUE impide valores duplicados no nulos, pero MySQL permite varios NULL en la misma columna.

CREATE UNIQUE INDEX idx_clientes_email
ON clientes (email);

Con este índice, MySQL no permitirá dos clientes con el mismo email.

Crear un índice compuesto

Un índice compuesto usa varias columnas.

CREATE INDEX idx_pedidos_cliente_fecha
ON pedidos (cliente_id, fecha);

Puede ayudar a consultas como:

SELECT *
FROM pedidos
WHERE cliente_id = 10
ORDER BY fecha DESC;

El orden de las columnas depende de los filtros, los joins y la ordenación de tus consultas reales, no de una regla fija. MySQL puede usar los prefijos izquierdos del índice: uno sobre (cliente_id, fecha) sirve para búsquedas por cliente_id y por la combinación (cliente_id, fecha), pero normalmente no para búsquedas por fecha por sí sola.

Crear un índice FULLTEXT

FULLTEXT sirve para búsquedas por términos sobre columnas CHAR, VARCHAR o TEXT en tablas InnoDB o MyISAM.

CREATE FULLTEXT INDEX idx_articulos_contenido
ON articulos (titulo, contenido);

Consulta:

SELECT *
FROM articulos
WHERE MATCH(titulo, contenido) AGAINST('mysql indices');

No lo consideres un sustituto universal de LIKE: FULLTEXT está pensado para búsqueda por palabras, no para coincidencias de patrones arbitrarios.

Ver índices de una tabla

SHOW INDEX FROM clientes;

En el resultado son útiles Key_name (nombre del índice), Seq_in_index (posición de la columna dentro del índice), Non_unique (si admite duplicados), Cardinality (estimación de valores distintos), Sub_part (longitud de prefijo, si la hay), Visible (si el optimizador puede usarlo) e Index_type (estructura, normalmente BTREE).

Eliminar un índice

DROP INDEX idx_clientes_ciudad ON clientes;

Antes de eliminarlo, comprueba que ninguna consulta relevante lo esté aprovechando.

Cuándo crear índices y cuándo no

Crea índices en columnas que uses a menudo en:

WHERE.
JOIN.
ORDER BY.
GROUP BY.
Restricciones UNIQUE.

No conviene indexar todo: cada índice ocupa espacio y ralentiza las escrituras (INSERT, UPDATE y DELETE) porque MySQL debe mantenerlo actualizado.

Evita índices innecesarios en tablas muy pequeñas, en columnas que casi nunca se filtran y en índices duplicados o muy parecidos. Que una columna tenga pocos valores distintos no es un motivo absoluto para descartar el índice: decide según tus consultas y la distribución real de los datos.

Comprobar si se usa un índice con EXPLAIN

Usa EXPLAIN para ver cómo MySQL ejecuta una consulta.

EXPLAIN
SELECT *
FROM clientes
WHERE ciudad = 'Madrid';

Los campos principales del resultado son:

possible_keys: índices candidatos para la consulta.
key: índice que el optimizador ha elegido.
rows: estimación de filas que examinará.
filtered: porcentaje estimado de filas que pasarán los filtros.
Extra: información adicional del plan.

La presencia de un índice no obliga al optimizador a usarlo. Si el plan no usa el índice esperado, revisa la consulta, el orden de las columnas del índice y la cantidad de datos. MySQL 8.4 dispone además de EXPLAIN ANALYZE para contrastar las estimaciones con la ejecución real.

Mide tus consultas con datos representativos y revisa si tienes índices redundantes. Cuando el índice forme parte del diseño inicial, revisa también cómo declararlo junto con claves y restricciones al crear una tabla en MySQL.

Preguntas frecuentes

¿MySQL crea automáticamente un índice para una clave foránea?

En InnoDB, las columnas de la tabla hija necesitan un índice: se reutiliza uno compatible que tenga las columnas de la clave foránea como prefijo izquierdo y en el mismo orden, o se crea automáticamente si falta. Por ejemplo, (a, b, c) sirve para una clave foránea (a, b), pero no para (b, a). Las columnas referenciadas de la tabla padre también necesitan un índice adecuado; define preferentemente una PRIMARY KEY o una restricción UNIQUE y no des por hecho que MySQL creará ese índice por ti.