Índice funcional

Un índice funcional (o expression index) permite indexar el resultado de una expresión o función en lugar de una columna directamente. Introducido en MySQL 8.0.13, resuelve el problema de que los índices normales no se usan cuando la columna aparece dentro de una función en el WHERE.

El problema

Cuando usas una función sobre una columna indexada en el WHERE, MySQL no puede usar el índice:

CREATE INDEX idx_fecha ON pedidos (fecha_pedido);
 
-- Esta consulta NO puede usar el índice
EXPLAIN SELECT * FROM pedidos WHERE YEAR(fecha_pedido) = 2025;

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

Aunque fecha_pedido tiene un índice, YEAR(fecha_pedido) es una expresión que MySQL no puede resolver con ese índice. La solución tradicional es reescribir la consulta:

-- Reescritura para usar el índice (sin función sobre la columna)
EXPLAIN SELECT * FROM pedidos
WHERE fecha_pedido >= '2025-01-01' AND fecha_pedido < '2026-01-01';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	range	idx_fecha	idx_fecha	20	Using index condition

Pero con un índice funcional, no necesitas reescribir la consulta.

Sintaxis

CREATE INDEX nombre_indice ON tabla ((expresion));
-- Nota: la expresión va entre paréntesis dobles

Ejemplo básico

-- Crear índice funcional sobre YEAR(fecha_pedido)
CREATE INDEX idx_anio_pedido ON pedidos ((YEAR(fecha_pedido)));
 
-- Ahora la consulta con YEAR() usa el índice
EXPLAIN SELECT * FROM pedidos WHERE YEAR(fecha_pedido) = 2025;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	ref	idx_anio_pedido	idx_anio_pedido	20	NULL

Caso de uso: búsqueda case-insensitive

-- Sin índice funcional: LOWER() impide usar el índice
EXPLAIN SELECT * FROM productos WHERE LOWER(nombre) = 'iphone 15 pro';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	productos	ALL	NULL	NULL	30	Using where

-- Crear índice funcional sobre LOWER(nombre)
CREATE INDEX idx_nombre_lower ON productos ((LOWER(nombre)));
 
EXPLAIN SELECT * FROM productos WHERE LOWER(nombre) = 'iphone 15 pro';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	productos	ref	idx_nombre_lower	idx_nombre_lower	1	NULL

Caso de uso: extraer mes y año

-- Índice para consultas agrupadas por mes
CREATE INDEX idx_mes_pedido ON pedidos ((DATE_FORMAT(fecha_pedido, '%Y-%m')));
 
EXPLAIN SELECT
    DATE_FORMAT(fecha_pedido, '%Y-%m') AS mes,
    COUNT(*) AS num_pedidos,
    SUM(total) AS ventas
FROM pedidos
WHERE DATE_FORMAT(fecha_pedido, '%Y-%m') = '2025-11'
GROUP BY DATE_FORMAT(fecha_pedido, '%Y-%m');

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	pedidos	ref	idx_mes_pedido	idx_mes_pedido	6	Using where

Caso de uso: cálculos

-- Índice sobre precio con descuento calculado
CREATE INDEX idx_precio_descuento ON productos ((precio * 0.9));
 
-- Buscar productos cuyo precio con 10% de descuento sea menor a 50
EXPLAIN SELECT nombre, precio, precio * 0.9 AS precio_descuento
FROM productos
WHERE precio * 0.9 < 50;

Índice funcional compuesto

Puedes combinar expresiones con columnas regulares:

-- Índice compuesto: categoría + año de creación
CREATE INDEX idx_cat_anio ON productos (categoria_id, (YEAR(creado_en)));
 
EXPLAIN SELECT nombre, precio
FROM productos
WHERE categoria_id = 6 AND YEAR(creado_en) = 2025;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	productos	ref	idx_cat_anio,categoria_id	idx_cat_anio	4	Using where

Caso de uso: JSON

Los índices funcionales son especialmente útiles con columnas JSON:

CREATE TABLE configuraciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    datos JSON NOT NULL
);
 
INSERT INTO configuraciones (datos) VALUES
('{"idioma": "es", "tema": "oscuro"}'),
('{"idioma": "en", "tema": "claro"}'),
('{"idioma": "es", "tema": "claro"}');
 
-- Índice funcional sobre un campo JSON
CREATE INDEX idx_idioma ON configuraciones ((CAST(datos->>'$.idioma' AS CHAR(10))));
 
EXPLAIN SELECT * FROM configuraciones
WHERE datos->>'$.idioma' = 'es';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	configuraciones	ref	idx_idioma	idx_idioma	2	Using where

Restricciones

La expresión debe ser determinista (mismo input → mismo output)
No se permiten funciones no deterministas como NOW(), RAND(), UUID()
La expresión no puede contener subconsultas
La expresión debe coincidir exactamente con la del WHERE para que se use el índice
Requiere MySQL 8.0.13 o superior

Alternativa: columnas generadas

Antes de MySQL 8.0.13, la alternativa era usar columnas generadas:

-- Columna generada + índice normal (funciona en MySQL 5.7+)
ALTER TABLE pedidos ADD COLUMN anio_pedido INT
    GENERATED ALWAYS AS (YEAR(fecha_pedido)) STORED;
 
CREATE INDEX idx_anio ON pedidos (anio_pedido);

Los índices funcionales son más elegantes porque no requieren modificar la estructura de la tabla.

Limpieza

DROP INDEX idx_fecha ON pedidos;
DROP INDEX idx_anio_pedido ON pedidos;
DROP INDEX idx_nombre_lower ON productos;
DROP INDEX idx_mes_pedido ON pedidos;
DROP INDEX idx_precio_descuento ON productos;
DROP INDEX idx_cat_anio ON productos;
DROP TABLE IF EXISTS configuraciones;

En el siguiente artículo veremos DROP INDEX y las diferentes formas de eliminar índices.

Escrito por Eduardo Lázaro