Índices sobre expresiones o índices funcionales

CREATE INDEX idx_clientes_email_lower
ON clientes(lower(email));

SELECT id, nombre, email
FROM clientes
WHERE lower(email) = 'ana@example.test';

Modelo mental: indexa la forma en la que realmente buscas

Un índice convencional sobre email contiene el valor de la columna. Si tu condición siempre aplica lower(email), la expresión consultada ya no es simplemente email. Un índice funcional permite indexar esa forma derivada.

Ejemplo comprobado: búsqueda de email sin distinguir mayúsculas

Sobre la base educativa, el siguiente índice y consulta fueron comprobados dentro de una transacción y revertidos después de la prueba:

CREATE INDEX idx_clientes_email_lower_demo
ON clientes(lower(email));

EXPLAIN QUERY PLAN
SELECT id, nombre, email
FROM clientes
WHERE lower(email) = 'ana@example.test';

El plan confirma que SQLite reconoce el índice para la expresión consultada. La consulta devuelve a Ana Torres con ana@example.test.

La expresión de la consulta debe encajar con la indexada

SQLite documenta una regla especialmente estricta: el planificador no hace álgebra para demostrar que dos expresiones equivalentes son la misma. Si indexas x + y y consultas y + x, puede no usar el índice aunque la suma sea matemáticamente equivalente.

En la práctica, conserva una forma estable para las expresiones indexadas y comprueba el plan real con EXPLAIN o la herramienta equivalente del motor.

Diferencias entre motores

• PostgreSQL: CREATE INDEX acepta expresiones y exige que las funciones y operadores usados en la definición sean inmutables.
• SQLite: permite índices sobre expresiones deterministas y exige una coincidencia muy cercana entre la expresión del índice y la de la consulta.
• MySQL 8.4: admite functional key parts; la expresión se escribe entre paréntesis adicionales, por ejemplo CREATE INDEX idx ON tabla ((LOWER(columna))), y hereda restricciones de columnas generadas.
• SQL Server: el patrón habitual es definir una columna calculada que cumpla requisitos de determinismo/precisión y crear el índice sobre esa columna.

Por eso «índice funcional» describe una intención común, pero la sintaxis exacta no es portable entre los cuatro motores.

Cuándo merece la pena

• Filtros frecuentes por LOWER(columna) u otra normalización estable.
• Ordenaciones repetidas por una expresión calculada.
• Reglas de unicidad sobre una transformación cuando el motor lo permite y la semántica está bien definida.

No lo confundas con un índice compuesto, que combina varias claves, ni con un índice parcial/filtrado, que incluye solo las filas que cumplen un predicado. Un índice puede combinar varias ideas, pero cada una resuelve un problema distinto.

Práctica: indexa una búsqueda normalizada por ciudad

CREATE INDEX idx_clientes_ciudad_lower
ON clientes(lower(ciudad));

SELECT id, nombre, ciudad
FROM clientes
WHERE lower(ciudad) = 'madrid'
ORDER BY id;

Conceptos relacionados

Fuentes técnicas consultadas

Las referencias oficiales se usaron para verificar sintaxis, restricciones y comportamiento del planificador.

• SQLite: Indexes On Expressions
• PostgreSQL: CREATE INDEX
• MySQL 8.4: CREATE INDEX y Functional Key Parts
• SQL Server: Indexes on computed columns