Filtrar por valores calculados

El enunciado parece inocente: esta consulta es correcta, pero lenta; ¿por qué? A menudo, la respuesta es que la cláusula WHERE envuelve una columna indexada en una función. Eso hace que el predicado sea no sargable: el optimizador ya no puede usar el índice y debe recorrer todas las filas.

En esta lección se explica la sargabilidad, se muestran las reescrituras que esperan los entrevistadores y se aborda dónde debe colocarse realmente un filtro calculado.

Sargable (Search ARGument ABLE) significa que un predicado puede usar un índice para buscar directamente las filas coincidentes. Como regla práctica, la columna indexada debe aparecer sin modificaciones en uno de los lados de la comparación, no estar enterrada dentro de una función o expresión.

• Sargable: col = 5, col > 100, col LIKE 'abc%'
• No sargable: FUNC(col) = 5, col + 1 > 100

Aquí se busca obtener los pedidos realizados en 2024. Envolver la columna en YEAR() obliga al motor a calcular el año para todas y cada una de las filas antes de poder compararlo, por lo que el índice sobre order_date no sirve.

Devuelve el resultado correcto, pero recorre toda la tabla. En una tabla grande, esto supone la diferencia entre milisegundos y minutos.

La solución consiste en dejar order_date sin modificaciones y expresar la condición como un intervalo semiabierto. Así, el índice sobre order_date puede buscar directamente el inicio de 2024 y detenerse en 2025.

El resultado es el mismo, pero se hace un escaneo de rango del índice en lugar de un escaneo completo. Esta reescritura mediante intervalos es la corrección de sargabilidad que más se evalúa en las entrevistas.

El mismo problema aparece en las operaciones aritméticas. WHERE salary + bonus > 100000 o WHERE price * 0.9 < 50 calculan sobre la columna y bloquean el uso del índice.

Traslade las operaciones al lado de la constante siempre que sea posible: reescriba price * 0.9 < 50 como price < 50 / 0.9. El literal se calcula una vez y price permanece sin modificaciones y puede usar el índice.

WHERE LOWER(email) = 'a@b.com' no es sargable frente a un índice normal sobre email, porque primero se convierten a minúsculas los correos de todas las filas.

Hay dos soluciones para producción: almacenar una copia normalizada en minúsculas e indexarla, o crear un índice funcional sobre LOWER(email) para indexar la propia expresión. Mencionar la opción del índice funcional demuestra experiencia en entornos reales.

A veces el filtro depende realmente de un valor calculado para el que no existe una reescritura como intervalo; por ejemplo, al filtrar por una proporción. Aun así, no puede hacer referencia a un alias de SELECT en WHERE, porque WHERE se evalúa antes que la lista de SELECT.

Por tanto, debe repetir la expresión en WHERE o envolver la consulta en una subconsulta / CTE y filtrar la columna calculada en la consulta exterior.

Un cálculo que es una agregación no puede estar en WHERE en absoluto, porque WHERE filtra filas individuales antes de que se realice la agrupación. WHERE SUM(amount) > 1000 produce un error.

Los filtros de agregación deben estar en HAVING, que se ejecuta después de GROUP BY. Saber qué cláusula tiene acceso al cálculo es, por sí mismo, una pregunta frecuente sobre el orden de ejecución.

Le muestran una consulta lenta que aplica una función a una columna y le piden que la acelere sin cambiar el resultado. Su enfoque:

• Identificar que aplicar una función a la columna la hace no sargable
• Reescribir la consulta para mantener la columna sin modificaciones (mediante un intervalo o trasladando las operaciones al lado de la constante)
• Si no existe una reescritura, proponer un índice funcional o una columna calculada almacenada

Mencionar EXPLAIN para confirmar que el plan cambió de un escaneo secuencial a un escaneo de índice completa la respuesta.

Sea equilibrado: los índices y los índices funcionales aceleran las lecturas, pero ralentizan las escrituras y consumen almacenamiento. En una tabla pequeña, un escaneo completo está bien y añadir un índice supone un esfuerzo desperdiciado.

La respuesta de un perfil sénior es condicional: si esta columna es grande y se filtra con frecuencia de esta manera, haga que el predicado sea sargable o añada un índice funcional; de lo contrario, déjelo como está. En las entrevistas, el contexto es más importante que seguir reglas dogmáticas.

A veces realmente necesita filtrar por un valor transformado, por ejemplo, para hacer una comparación sin distinguir mayúsculas de minúsculas. En lugar de renunciar a los índices, cree un índice de expresión (funcional) sobre la expresión exacta por la que filtra.

• De este modo, el optimizador puede utilizar el índice aunque una función envuelva la columna.
• La expresión del índice debe coincidir exactamente con la expresión del predicado.

Identifique qué predicado puede indexar el optimizador.

Conclusiones principales:

• Un predicado es sargable cuando la columna indexada aparece sin modificaciones, no dentro de una función u operación aritmética
• Reescriba YEAR(col) = 2024 como un intervalo semiabierto; traslade las operaciones al lado de la constante
• Para expresiones inevitables, use un índice funcional o una columna calculada almacenada
• No puede usar un alias de SELECT en WHERE; las agregaciones van en HAVING

La pregunta clásica presenta una consulta lenta; la solución clásica consiste en mantener la columna sin modificaciones.