Cuando los índices no entran en juego — patrones y soluciones

Patrones donde existe un índice pero no entra en juego

Incluso después de crear un índice, ciertas formas de escribir la condición impiden que se use y la consulta sigue ejecutándose como un escaneo completo de tabla (SCAN).

Los tres culpables clásicos están abajo.

• Condiciones que envuelven la columna en una función o aritmética (substr(col) / upper(col) / col + 0, etcétera)
• Coincidencia intermedia LIKE '%x%'
• Condiciones de negación (<> / NOT IN)

En todos los casos la causa es la misma: la comparación ya no se alinea con el orden almacenado en el índice.

Más adelante en el artículo veremos los índices de expresión para los casos en los que no puedes evitar envolver la columna en una función, y los índices parciales para acotar el índice a un subconjunto específico de filas.

A lo largo de este artículo compararás "la forma que no usa el índice" con "la forma que sí lo usa" lado a lado en el plan de consulta.

-- Incluso con un índice sobre amount,
-- envolver la columna en una función recae en un escaneo completo de tabla
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- Compara la columna tal cual → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Envuelve la columna en una función → vuelve a SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE abs(amount) >= 800000;

Considera el requisito: "Quiero contar solo las ventas de 2024." Crea un índice sobre sale_date, luego compara dos planes de consulta lado a lado — uno que envuelve la columna en una función, y otro que usa una condición de rango con la columna tal cual — y observa cómo cambia SCAN frente a SEARCH. Reconstruye el índice dentro de esta única ejecución de la consola para que el plan sea totalmente autocontenido. (Ejecútalo correctamente para revelar la explicación.)

① Elimina cualquier índice existente con DROP INDEX IF EXISTS y crea un índice de una sola columna sobre sale_date.

② Usa EXPLAIN QUERY PLAN sobre una consulta que extrae el año de sale_date con substr y lo compara con '2024' (la forma que envuelve la columna en una función).

③ Luego muestra el plan para una consulta que expresa el mismo significado como una condición de rango sobre sale_date (mayor o igual al inicio de 2024, menor que el inicio del año siguiente) y compáralo con ②.

LIKE con coincidencia intermedia

Un LIKE con coincidencia por prefijo (una forma donde el inicio está fijado, como 'P00%') puede en principio acotar un rango usando el orden del índice.

En cambio, una coincidencia intermedia ('%5%') o coincidencia por sufijo ('%50') no tiene un inicio fijado, así que ninguna base de datos puede recorrer el índice y la consulta se convierte en un escaneo completo de tabla.

Dicho esto, en SQLite, `LIKE` es por defecto insensible a mayúsculas, así que no se alinea con el índice ordenado por BINARY habitual — incluso un LIKE con coincidencia por prefijo no obtiene un SEARCH y recae en SCAN (mira el aviso de abajo).

Así que cuando quieras que una coincidencia por prefijo en SQLite use una búsqueda por índice, la solución directa es reescribirla como una condición de rango, como product >= 'P00' AND product < 'P01'.

Para LIKE con coincidencia intermedia, si realmente lo necesitas como requisito, considera un mecanismo diferente como la búsqueda de texto completo.

El LIKE de SQLite es insensible a mayúsculas por defecto

En SQLite, PRAGMA case_sensitive_like está OFF por defecto, así que LIKE es una comparación insensible a mayúsculas.

Los índices se construyen usando el orden BINARY estándar (orden alfabético sensible a mayúsculas), así que incluso una coincidencia por prefijo como LIKE 'P00%' no se alinea con el orden del índice y la regla de comparación — no se usa como un SEARCH.

Si ejecutas LIKE 'P00%' en esta consola, el plan se convierte en SCAN perf_sales USING COVERING INDEX.

Las soluciones directas son reescribirlo como una condición de rango (product >= 'P00' AND product < 'P01'), o enumerar las variantes de mayúsculas/minúsculas en el lado del valor (product IN ('P050', 'p050')).

En este artículo vamos con la opción más directa — reescribirlo como una condición de rango.

En MySQL / PostgreSQL, si LIKE se configura como sensible a mayúsculas, una coincidencia por prefijo suele convertirse en un SEARCH.

-- Compara los planes de condición de rango y LIKE contra un índice sobre product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Condición de rango → inicio fijado, así que SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE product >= 'P15' AND product < 'P16';

-- LIKE con coincidencia por prefijo → SCAN en SQLite (insensible a mayúsculas)
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE 'P15%';

-- LIKE con coincidencia intermedia → SCAN en cualquier BD
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE '%9%';

Toma el requisito "Quiero contar las ventas cuyo código de producto empieza por un prefijo concreto" y compara los planes para tres formas: (A) una condición de rango, (B) un LIKE con coincidencia por prefijo, y (C) un LIKE con coincidencia intermedia. Crea un índice sobre product y observa qué forma se convierte en un SEARCH en SQLite.

① Elimina cualquier índice existente con DROP INDEX IF EXISTS y crea un índice de una sola columna sobre product.

② Usa EXPLAIN QUERY PLAN sobre una consulta que cuenta filas con la condición de rango WHERE product >= 'P00' AND product < 'P01'.

③ Muestra el plan para el mismo significado escrito como un LIKE con coincidencia por prefijo 'P00%' y compáralo con ②.

④ Luego también muestra el plan para un LIKE con coincidencia intermedia '%5%'.

Envolver la columna con upper() o aritmética impide que el índice se use

Cuando aplicas una función o aritmética a una columna para filtrarla, el índice de la columna no puede usarse aunque exista uno.

La razón es que el índice está ordenado por los valores en bruto de la columna, así que una comparación contra un valor transformado como upper(product) o amount + 10000 ya no se alinea con el orden del índice.

Por ejemplo, WHERE upper(product) = 'P050' tiene que pasar el product de cada fila a través de upper() antes de comparar, así que el índice sobre product no se usa y la consulta recae en un escaneo completo.

Del mismo modo, WHERE amount + 10000 = 900000 tiene que calcular amount + 10000 para cada fila antes de comparar, así que el índice sobre amount no se usa.

La solución es dejar la columna en paz y ajustar en el lado del valor en su lugar.

upper(product) = 'P050', si los únicos valores esperados son 'P050' y 'p050', se convierte en WHERE product IN ('P050', 'p050') — e IN permite al índice acotar varios valores a la vez.

amount + 10000 = 900000 puede reescribirse directamente como amount = 800000 restando 10000 a ambos lados, y el índice sobre amount lo acota de un tirón.

Reescribir substr(sale_date, 1, 4) como una condición de rango en la sección anterior es la misma idea de "no transformar la columna" aplicada a las fechas.

-- Ejemplo de envolver la columna con lower() contra un índice sobre product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Envuelve la columna con lower() → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE lower(product) = 'p001';

-- Enumera variantes de mayúsculas/minúsculas en el lado del valor → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product IN ('P001', 'p001');

-- Ejemplo de envoltura aritmética (contra un índice sobre qty)
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_qty ON perf_sales(qty);

-- Envuelve con qty * 2 → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty * 2 = 20;

-- Simplifica la expresión en el lado del valor (divide ambos lados por 2) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty = 10;

Considera el requisito: "Quiero contar las ventas para el código de producto 'P050' (o su forma en minúsculas 'p050')."

Crea un índice sobre product y compara los planes para dos formas — envolver la columna con upper() frente a enumerar los valores candidatos con IN (...) en el lado del valor.

Ambas formas apuntan a la misma comparación que cubre 'P050' y 'p050', así que el resultado es idéntico.

① Elimina cualquier índice existente con DROP INDEX IF EXISTS y crea un índice de una sola columna sobre product.

② Usa EXPLAIN QUERY PLAN sobre una consulta que envuelve la columna con upper() como en WHERE upper(product) = 'P050'.

③ Muestra el plan para el mismo significado escrito como WHERE product IN ('P050', 'p050') y compáralo con ②.

Para el requisito "Quiero contar las ventas con un importe de exactamente 800 000", crea un índice sobre amount y compara los planes para dos formas — aplicar aritmética a la columna frente a ajustar en el lado del valor.

amount + 10000 = 900000 se convierte en amount = 800000 una vez que restas 10000 a ambos lados — matemáticamente completamente equivalente.

① Elimina cualquier índice existente con DROP INDEX IF EXISTS y crea un índice de una sola columna sobre amount.

② Usa EXPLAIN QUERY PLAN sobre una consulta que aplica aritmética a la columna como en WHERE amount + 10000 = 900000.

③ Muestra el plan para el mismo significado escrito como WHERE amount = 800000 y compáralo con ②.

Condiciones de negación (<>, NOT IN)

Las condiciones de negación como <> (distinto de) o NOT IN suelen acabar apuntando a "la mayor parte de la tabla", así que no tiene sentido acotar con un índice.

Por ejemplo, cuando status es 'paid' (alrededor de 40 000 de 50 000 filas) / 'pending' (alrededor de 5 000) / 'refunded' (alrededor de 5 000), WHERE status <> 'paid' apunta a 10 000 filas — alrededor del 20 % de la tabla.

La selectividad no es lo bastante baja, así que el optimizador decide que un escaneo completo de tabla es más barato.

La solución es reescribir como una condición de igualdad.

WHERE status <> 'paid' puede reescribirse como WHERE status IN ('pending', 'refunded'), y esa forma puede acotar las filas objetivo vía el índice.

-- Compara NOT IN frente a reescritura con IN contra un índice sobre status
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status);

-- Condición de negación (NOT IN) → objetivo amplio, SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status NOT IN ('refunded');

-- Reescrito como igualdad (IN) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status IN ('paid', 'pending');

Considera el requisito: "Quiero contar las ventas que no sean paid." Crea un índice sobre status y compara los planes entre escribirlo con <> y reescribir el mismo significado como una condición de igualdad con IN.

① Elimina cualquier índice existente con DROP INDEX IF EXISTS y crea un índice de una sola columna sobre status.

② Usa EXPLAIN QUERY PLAN sobre una consulta que cuenta filas que coinciden con WHERE status <> 'paid'.

③ Muestra el plan para el mismo significado reescrito como WHERE status IN ('pending', 'refunded') y compáralo con ②.

Arréglalo con índices de expresión e índices parciales

Cuando envolver la columna en una función es inevitable, un índice de expresión (un índice construido sobre el resultado de una expresión, no sobre la columna en bruto) salva el día.

Con CREATE INDEX ix ON perf_sales(substr(sale_date,1,4)), crear un índice sobre la misma expresión que la condición usa convierte la comparación sobre esa expresión en una búsqueda por índice.

El requisito es que la expresión en la condición coincida con la expresión en el índice.

La otra herramienta es un índice parcial (un índice con una cláusula WHERE, construido solo para un subconjunto específico de filas).

Si las filas con status='pending' son solo una parte de la tabla (5 000 de 50 000), puedes construir un índice acotado como CREATE INDEX ix ON perf_sales(emp_id) WHERE status='pending'.

El índice en sí se mantiene pequeño y acota eficientemente las consultas que incluyen esa condición.

-- Índice de expresión: constrúyelo sobre la misma expresión que usa la condición
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(substr(sale_date, 1, 4));

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE substr(sale_date, 1, 4) = '2024';

Considera el requisito: "Quiero consultar las ventas en curso (pending) acotadas por comercial." Pending es un subconjunto pequeño (5 000 de 50 000), así que construye un índice parcial que apunte solo a esas filas y confirma que un plan de consulta que incluya status='pending' se convierte en una búsqueda por índice.

① Elimina cualquier índice existente con DROP INDEX IF EXISTS.

② Sobre emp_id, crea un índice parcial (usando la forma CREATE INDEX ... WHERE ...) que apunte solo a las filas donde status es igual a 'pending'.

③ Usa EXPLAIN QUERY PLAN sobre una consulta que cuenta las filas donde status es 'pending' y un emp_id específico, y confirma que el índice parcial se está usando.