Índice compuesto

Un índice compuesto (o multicolumna) incluye dos o más columnas en un solo índice. MySQL puede usarlo cuando la consulta filtra, ordena o agrupa por las columnas incluidas, siguiendo el orden en que fueron definidas. La regla más importante es la del prefijo izquierdo: MySQL solo puede usar el índice desde la columna más a la izquierda en adelante.

Sintaxis

CREATE INDEX nombre_indice ON tabla (columna1, columna2, columna3);

Ejemplo básico

-- Crear índice compuesto en pedidos
CREATE INDEX idx_pedidos_estado_fecha ON pedidos (estado, fecha_pedido);
 
-- Esta consulta usa las dos columnas del índice
EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha_pedido > '2025-12-01';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	range	idx_pedidos_estado_fecha	idx_pedidos_estado_fecha	5	Using index condition

MySQL usa el índice compuesto para filtrar por estado y luego por fecha dentro de ese estado.

Regla del prefijo izquierdo

MySQL solo puede usar las columnas del índice de izquierda a derecha. Para un índice (A, B, C):

Consulta	Usa el índice
WHERE A = 1	Sí (usa A)
WHERE A = 1 AND B = 2	Sí (usa A, B)
WHERE A = 1 AND B = 2 AND C = 3	Sí (usa A, B, C)
WHERE B = 2	No (falta A)
WHERE C = 3	No (faltan A y B)
WHERE A = 1 AND C = 3	Parcial (usa solo A)
WHERE B = 2 AND C = 3	No (falta A)

-- Usa el índice (prefijo izquierdo: estado)
EXPLAIN SELECT * FROM pedidos WHERE estado = 'enviado';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	ref	idx_pedidos_estado_fecha	idx_pedidos_estado_fecha	3	NULL

-- NO usa el índice (no empieza por la columna izquierda)
EXPLAIN SELECT * FROM pedidos WHERE fecha_pedido > '2025-12-01';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	ALL	NULL	NULL	25	Using where

type: ALL confirma el full table scan. La consulta necesita su propio índice sobre fecha_pedido.

Orden de columnas

El orden de las columnas en el índice importa. La regla general es:

Columnas de igualdad primero (=): proporcionan el filtro más estrecho
Columnas de rango después (>, <, BETWEEN): el rango "rompe" el uso del índice para las columnas siguientes

-- Índice para: estado = ? AND fecha_pedido BETWEEN ? AND ?
-- Orden correcto: (estado, fecha_pedido)
-- Incorrecto: (fecha_pedido, estado) - el rango en fecha impide usar estado
 
CREATE INDEX idx_cliente_estado ON pedidos (cliente_id, estado);
 
-- Usa ambas columnas
EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1 AND estado = 'entregado';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	ref	idx_cliente_estado,cliente_id	idx_cliente_estado	2	NULL

Índice compuesto vs múltiples índices

MySQL generalmente solo usa un índice por tabla por consulta (con excepciones como index merge). Un índice compuesto bien diseñado suele ser mejor que varios índices individuales:

-- Dos índices separados
CREATE INDEX idx_a ON pedidos (estado);
CREATE INDEX idx_b ON pedidos (fecha_pedido);
 
-- Un índice compuesto (generalmente mejor)
CREATE INDEX idx_ab ON pedidos (estado, fecha_pedido);

Estrategia	Para WHERE estado = ? AND fecha_pedido > ?
Solo idx_a	Filtra por estado, luego escanea por fecha
Solo idx_b	Filtra por fecha, luego escanea por estado
idx_ab compuesto	Filtra por ambos eficientemente

Índice compuesto para ORDER BY

-- Índice que cubre filtro y ordenamiento
CREATE INDEX idx_estado_fecha ON pedidos (estado, fecha_pedido);
 
-- El índice cubre el WHERE y el ORDER BY
EXPLAIN SELECT id, fecha_pedido, total
FROM pedidos
WHERE estado = 'pendiente'
ORDER BY fecha_pedido DESC;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	ref	idx_estado_fecha	idx_estado_fecha	7	Backward index scan

Backward index scan indica que MySQL recorre el índice en orden inverso para resolver el ORDER BY DESC sin un paso de ordenamiento adicional.

Ejemplo práctico: consulta de productos

-- Consulta frecuente: productos activos de una categoría, ordenados por precio
CREATE INDEX idx_prod_cat_activo_precio ON productos (categoria_id, activo, precio);
 
EXPLAIN SELECT nombre, precio
FROM productos
WHERE categoria_id = 6 AND activo = TRUE
ORDER BY precio;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	productos	ref	idx_prod_cat_activo_precio	idx_prod_cat_activo_precio	4	Using index condition

El índice filtra por categoría, luego por activo, y los resultados ya salen ordenados por precio.

Cuántas columnas incluir

No hay un límite práctico estricto, pero:

Columnas	Recomendación
2-3	Ideal para la mayoría de consultas
4-5	Aceptable si la consulta lo justifica
6+	Rara vez necesario, puede indicar un problema de diseño

Cada columna adicional aumenta el tamaño del índice y ralentiza las escrituras.

Limpieza

DROP INDEX idx_pedidos_estado_fecha ON pedidos;
DROP INDEX idx_cliente_estado ON pedidos;
DROP INDEX idx_a ON pedidos;
DROP INDEX idx_b ON pedidos;
DROP INDEX idx_ab ON pedidos;
DROP INDEX idx_estado_fecha ON pedidos;
DROP INDEX idx_prod_cat_activo_precio ON productos;

En el siguiente artículo veremos los índices únicos, que combinan la velocidad de búsqueda con la garantía de no duplicados.

Escrito por Eduardo Lázaro