Índices SQL: 6 ejercicios para GSI A2 con SQLite

Índices SQL y planes: qué se está observando

Un índice es una estructura auxiliar que puede ayudar a localizar u ordenar datos para una consulta. Su utilidad depende de las consultas reales y debe valorarse junto al espacio y el trabajo adicional de mantenimiento. En SQLite, EXPLAIN QUERY PLAN (abreviado a menudo como EQP) presenta una descripción de alto nivel de la estrategia elegida. La fuente primaria es la documentación de EXPLAIN QUERY PLAN, complementada por la explicación del planificador de consultas y la de índices por expresión.

EQP no ejecuta la consulta que sigue. Por tanto no ofrece tiempo real, número real de filas leídas ni una demostración de rendimiento. Su salida textual puede cambiar entre versiones de SQLite, y el plan puede cambiar con los datos y las estadísticas disponibles. Aquí se comprueba el detalle observado con SQLite 3.46.1, mediante sqlite3 de la biblioteca estándar de Python, el 11 de noviembre de 2026. El método consiste en formular una hipótesis y contrastar el plan junto con el resultado de la consulta.

También hay que leer sus palabras con cuidado. SCAN puede recorrer una tabla o un índice; no significa necesariamente “malo”. SEARCH comunica que existe una búsqueda condicionada, pero no asegura una sola fila ni que el índice cubra todas las columnas solicitadas. Ver USING INDEX tampoco acredita una mejora por sí solo. Las decisiones de índice se validan junto a la corrección lógica, el volumen, la distribución y las escrituras que el sistema necesita soportar.

Estos seis ejercicios son práctica original para preparar razonamiento sobre SQL. El BOE de referencia, Anexo IX, incluye SQL (II.5) y administración de SGBD (IV.2); es la referencia de materia para esta práctica, pero no prescribe estos seis ejercicios ni los convierte en preguntas oficiales. No predice convocatorias ni resultados. Como base de SQL, antes conviene repasar JOIN, NULL y GROUP BY y, para consultas con resultados intermedios, el artículo de CTE y WITH RECURSIVE.

Preparación reproducible para cada ejercicio

Cada caso crea una base nueva en memoria y ejecuta este bloque, sin datos de personas ni clientes. Genera exactamente 1.100 expedientes ficticios mediante productos de diez dígitos; id va de 1 a 1.100. Ejecuta el bloque completo antes de la consulta concreta del ejercicio. Aislar la base evita que un índice creado en un caso altere el plan del siguiente.

CREATE TABLE digitos (d INTEGER PRIMARY KEY);
INSERT INTO digitos VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE expedientes (
  id INTEGER PRIMARY KEY,
  provincia INTEGER NOT NULL,
  estado TEXT NOT NULL,
  fecha TEXT NOT NULL,
  prioridad INTEGER NOT NULL,
  asunto TEXT NOT NULL
);

INSERT INTO expedientes (id, provincia, estado, fecha, prioridad, asunto)
SELECT n + 1,
       (n % 11) + 1,
       CASE n % 3 WHEN 0 THEN 'abierto' WHEN 1 THEN 'revision' ELSE 'cerrado' END,
       printf('2026-%02d-%02d', ((n / 31) % 12) + 1, (n % 28) + 1),
       (n % 5) + 1,
       printf('Aviso %04d', n + 1)
FROM (
  SELECT a.d + 10*b.d + 100*c.d + 1000*d.d AS n
  FROM digitos AS a CROSS JOIN digitos AS b CROSS JOIN digitos AS c CROSS JOIN digitos AS d
)
WHERE n < 1100;

La comprobación editorial reconstruyó este bloque para cada caso y contrastó tanto los resultados como los detalles de EQP. Las comparaciones antes y después usan la misma consulta y los mismos datos. Las consultas finales llevan ORDER BY cuando se muestran varias filas: sin él el orden no es una propiedad comprobable de SQL.

1. SCAN antes y SEARCH después de un índice sencillo

Pregunta

¿Cuántos expedientes pertenecen a la provincia 7 y cómo cambia el plan al indexar provincia?

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM expedientes WHERE provincia = 7;
-- Plan observado sin índice: SCAN expedientes

CREATE INDEX idx_expedientes_provincia ON expedientes(provincia);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM expedientes WHERE provincia = 7;
-- Plan observado: SEARCH expedientes USING COVERING INDEX idx_expedientes_provincia (provincia=?)

SELECT COUNT(*) FROM expedientes WHERE provincia = 7;

Resultado exacto: 100 antes y después.

Razonamiento. Antes no existe una estructura específica para el predicado y el plan observado recorre expedientes. Después SQLite puede buscar por el valor de provincia en idx_expedientes_provincia. En esta consulta COUNT(*) puede resolverse con las entradas del índice, de ahí el detalle COVERING observado. No se deduce de ello una regla de que todo SCAN sea deficiente: una tabla pequeña, una condición poco selectiva o un coste de mantenimiento pueden justificar otra decisión.

2. INTEGER PRIMARY KEY y rowid

Pregunta

¿Cómo busca SQLite una fila por la clave id declarada como INTEGER PRIMARY KEY?

EXPLAIN QUERY PLAN
SELECT id, asunto, estado
FROM expedientes
WHERE id = 731;
-- Plan observado: SEARCH expedientes USING INTEGER PRIMARY KEY (rowid=?)

SELECT id, asunto, estado
FROM expedientes
WHERE id = 731;

Resultado exacto:

731|Aviso 0731|revision

Razonamiento. En una tabla ordinaria de SQLite, una columna declarada exactamente INTEGER PRIMARY KEY es un alias del rowid. El plan observado nombra esa búsqueda. No hace falta crear otro índice duplicado sobre id para este caso. La idea es distinta de “toda clave primaria es idéntica”: la sintaxis concreta y el diseño de la tabla importan; no extrapoles este detalle a cualquier definición ni a otros motores.

3. Índice compuesto y prefijo izquierdo como hipótesis

Pregunta

Para expedientes de provincia 4 y estado abierto, ¿qué aporta un índice en (provincia, estado)?

CREATE INDEX idx_expedientes_provincia_estado
ON expedientes(provincia, estado);

EXPLAIN QUERY PLAN
SELECT COUNT(*)
FROM expedientes
WHERE provincia = 4 AND estado = 'abierto';
-- Plan observado: SEARCH expedientes USING COVERING INDEX idx_expedientes_provincia_estado (provincia=? AND estado=?)

SELECT COUNT(*)
FROM expedientes
WHERE provincia = 4 AND estado = 'abierto';

Resultado exacto: 34.

Razonamiento. El orden del índice se declara, no es decorativo. En este predicado se fija la primera columna y luego la segunda; el plan observado puede aplicar ambas restricciones. En un caso simple, el “prefijo izquierdo” ayuda a razonar: provincia es el comienzo de (provincia, estado). Pero no es una ley universal sobre todos los planes. SQLite puede aplicar transformaciones, elegir otro acceso o incluso usar skip-scan en determinados contextos y estadísticas; por eso no se debe afirmar que la única utilidad posible de un compuesto sea el prefijo inicial. Se mira EQP para la consulta concreta.

4. Índice de cobertura

Pregunta

¿Puede un índice (provincia, fecha) proporcionar tanto el filtro como las columnas devueltas?

CREATE INDEX idx_expedientes_provincia_fecha
ON expedientes(provincia, fecha);

EXPLAIN QUERY PLAN
SELECT fecha
FROM expedientes
WHERE provincia = 5
ORDER BY fecha ASC
LIMIT 3;
-- Plan observado: SEARCH expedientes USING COVERING INDEX idx_expedientes_provincia_fecha (provincia=?)

SELECT fecha
FROM expedientes
WHERE provincia = 5
ORDER BY fecha ASC
LIMIT 3;

Resultado exacto:

2026-01-05
2026-01-08
2026-01-09

Razonamiento. El índice contiene la clave de búsqueda, provincia, y el valor devuelto, fecha; el orden de sus entradas también encaja con el filtro de igualdad y el ORDER BY fecha. SQLite informa aquí COVERING INDEX, pero es una propiedad de esta lista SELECT: si se añadiera asunto, el mismo índice ya no contendría todo lo solicitado. “Cubrir” no es una etiqueta permanente de una tabla ni prueba una ventaja medible en cualquier carga.

5. Índice sobre una expresión

Pregunta

¿Cómo se busca el texto ASCII de estos datos sin distinguir mayúsculas y minúsculas usando una expresión indexada?

CREATE INDEX idx_expedientes_asunto_lower
ON expedientes(lower(asunto));

EXPLAIN QUERY PLAN
SELECT id, asunto
FROM expedientes
WHERE lower(asunto) = lower('AVISO 0420');
-- Plan observado: SEARCH expedientes USING INDEX idx_expedientes_asunto_lower (<expr>=?)

SELECT id, asunto
FROM expedientes
WHERE lower(asunto) = lower('AVISO 0420');

Resultado exacto:

420|Aviso 0420

Razonamiento. SQLite permite índices sobre expresiones deterministas, como documenta su fuente primaria. La consulta usa lower(asunto), la misma expresión indexada, por lo que el plan observado la busca mediante el índice. No basta con una expresión “parecida” en sentido matemático: el planificador no tiene por qué tratar a+b como si fuera b+a. Tampoco se indexan expresiones no deterministas en este mecanismo.

6. ORDER BY y árbol temporal

Pregunta

Para provincia 6, ¿cómo cambia el plan de ordenar fechas al añadir un índice compatible?

EXPLAIN QUERY PLAN
SELECT id, fecha FROM expedientes
WHERE provincia = 6
ORDER BY fecha ASC, id ASC
LIMIT 3;
-- Plan observado sin índice: SCAN expedientes; USE TEMP B-TREE FOR ORDER BY

CREATE INDEX idx_expedientes_provincia_fecha
ON expedientes(provincia, fecha);

EXPLAIN QUERY PLAN
SELECT id, fecha FROM expedientes
WHERE provincia = 6
ORDER BY fecha ASC, id ASC
LIMIT 3;
-- Plan observado: SEARCH expedientes USING COVERING INDEX idx_expedientes_provincia_fecha (provincia=?)

SELECT id, fecha FROM expedientes
WHERE provincia = 6
ORDER BY fecha ASC, id ASC
LIMIT 3;

Resultado exacto:

6|2026-01-06
765|2026-01-09
402|2026-01-10

Razonamiento. Sin índice compatible, SQLite observa un recorrido y un árbol B temporal para satisfacer la ordenación. Con (provincia, fecha), tras fijar provincia el tramo de índice ya está en orden de fecha; por eso el plan observado ya no muestra el árbol temporal. id es el rowid y actúa como desempate de entradas con la misma clave, pero la consulta final expresa id ASC para que la muestra sea determinista.

Cómo convertir los seis casos en práctica útil

Primero ejecuta cada bloque en una conexión nueva. Anota antes de crear el índice qué esperas ver: SCAN, SEARCH, un índice de cobertura o un árbol temporal. Después crea el índice, vuelve a ejecutar EQP y la consulta normal. Si el resultado lógico cambia al añadir un índice, el problema no es “optimización”: hay que revisar la consulta, el orden o los datos. Si el plan no cambia, tampoco fuerces una conclusión; investiga la selectividad, las estadísticas y la versión antes de acumular índices.

Los índices se diseñan para patrones de acceso, no como una lista de casillas. Un filtro de baja selectividad puede recorrer muchas entradas incluso con SEARCH; un SCAN de una estructura pequeña puede ser razonable; y cada índice adicional debe mantenerse al insertar, actualizar o borrar. La documentación de SQLite recomienda usar EQP como herramienta interactiva y no depender de su formato para aplicaciones, precisamente porque puede cambiar. Este artículo muestra detalles observados, no contratos de texto para automatización.

Preguntas frecuentes

¿Qué diferencia hay entre SCAN y SEARCH en EXPLAIN QUERY PLAN?

SCAN indica un recorrido de una tabla o de un índice; SEARCH indica que el plan busca usando una restricción. Ninguno expresa por sí solo cuántas filas habrá ni que el índice cubra la consulta.

¿Un índice garantiza que SQLite usará la consulta más rápida?

No. El plan depende de la consulta, los datos, las estadísticas y la versión de SQLite. Un índice puede no usarse o no mejorar el conjunto concreto.

¿Para qué sirve el orden de un índice compuesto?

El orden de sus columnas condiciona qué filtros y órdenes puede aprovechar. El prefijo izquierdo es una guía útil en casos simples, no una regla universal que sustituya observar el plan.

¿Qué es un índice de cobertura?

Es un índice que contiene los valores necesarios para resolver una consulta concreta; SQLite puede indicarlo como COVERING INDEX en el plan, pero conviene comprobarlo en la versión y consulta reales.

¿Puede SQLite indexar una expresión?

Sí, SQLite permite índices sobre expresiones deterministas. La expresión de la consulta debe corresponder a la indexada; una forma algebraicamente equivalente no tiene por qué coincidir para el planificador.

¿Estos ejercicios de índices son preguntas oficiales del GSI A2?

No. Son ejercicios originales de entrenamiento. El BOE de referencia incluye SQL y administración de SGBD en el programa, pero no impone estos casos ni predice preguntas de examen.

Siguiente práctica

Puedes pedir la muestra gratuita del Pack GSI A2 para revisar una parte del material antes de decidir. Es una muestra limitada, no el pack completo. El Pack GSI A2 ofrece práctica y apoyo con cobertura parcial del programa. Consulta en su ficha las secciones trabajadas y las que quedan fuera, y contrástalas con las bases y el temario aplicables.