Skip to main content

Indices, planificador y optimización

En SQLite, igual que en cualquier motor SQL, las consultas parecen simples, pero internamente requieren recorrer tablas, buscar valores, comparar filas y combinar información. Para que esto sea rápido y escalable, SQLite cuenta con dos mecanismos clave: los índices y el planificador de consultas.

Qué es un índice y por qué existen​

Un índice es una estructura interna que permite a SQLite encontrar filas rápidamente sin tener que recorrer toda la tabla. Funciona de una forma parecida al índice de un libro: en lugar de leer cada página, saltas directamente al punto relevante.

Ejemplo conceptual: si tienes una tabla de diez millones de usuarios, y haces una consulta como

SELECT * FROM users WHERE email = 'a@b.com';

Sin un índice, SQLite tendría que revisar cada fila hasta encontrar la que coincide. Esto se llama full table scan y es la causa más común de lentitud en bases de datos.

Con un índice sobre la columna email:

CREATE INDEX idx_users_email ON users(email);

SQLite puede saltar directamente hacia el valor buscado. El resultado es que consultas que tardaban segundos pasan a tardar milésimas.

Cómo funcionan los índices internamente​

SQLite utiliza estructuras B-Tree para almacenar tanto tablas como índices. La diferencia es que:

  • Una tabla almacena filas completas.
  • Un índice solo almacena claves ordenadas junto con punteros a las filas reales.

Esto significa que:

  • Un índice ocupa espacio extra.
  • Cuantos más índices tengas, más lento es insertar, actualizar o borrar datos.
  • Pero las consultas de lectura suelen acelerarse enormemente.

Por eso, crear índices sin pensar puede ser tan malo como no crearlos nunca. Más adelante decidirás cuándo convienen y cuándo no.

Cuándo conviene crear un índice​

Conviene crear un índice cuando:

  • Filtras por una columna con frecuencia.
  • Ordenas por esa columna a menudo.
  • Dicha columna tiene muchos valores distintos.

Ejemplo:

SELECT * FROM orders WHERE client_id = 7;

Si esta consulta aparece constantemente en tu API, debes crear un índice:

CREATE INDEX idx_orders_client_id ON orders(client_id);

Otro ejemplo:

SELECT * FROM logs ORDER BY created_at DESC LIMIT 50;

Organizar esta columna en un índice hace que el ORDER BY sea mucho más rápido.

Cuándo NO conviene crear un índice​

Hay casos donde un índice puede ser innecesario o incluso perjudicial:

  • Columnas con muy pocos valores distintos.

    Ejemplo: una columna boolean (0 o 1). Un índice apenas ayudará.

  • Tablas que reciben muchísimos INSERT.

    Cada índice implica trabajo extra en cada inserción.

  • Cuando la consulta rara vez se filtra por esa columna.

regla práctica: Indexa lo que filtras o ordenas con frecuencia. No indexes por si acaso.

Índices y claves foráneas​

En SQLite, declarar una FOREIGN KEY no crea un índice automáticamente. Para acelerar JOINs, búsquedas y validaciones internas, es conveniente crear índices manuales sobre cada columna usada como clave foránea.

Ejemplo:

FOREIGN KEY (client_id) REFERENCES clients(id)

Debería acompañarse de:

CREATE INDEX idx_orders_client_id ON orders(client_id);

Sin este índice, un JOIN como:

SELECT *
FROM orders o
JOIN clients c ON o.client_id = c.id;

puede ser muy lento.

Cómo saber si una consulta necesita un índice​

SQLite incluye la instrucción EXPLAIN QUERY PLAN, que muestra el plan que el motor usará para resolver una consulta.

Ejemplo:

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price > 100;

Si ves algo como:

SCAN TABLE products

significa que hará un recorrido completo de la tabla. Eso es aceptable en tablas pequeñas, pero problemático en tablas grandes.

Si en cambio ves:

SEARCH TABLE products USING INDEX idx_products_price

significa que está aprovechando el índice.

Interpretar el planificador te permite justificar cada índice que creas y detectar rápidamente cuellos de botella.

Cómo crear y borrar índices​

Crear índice:

CREATE INDEX idx_users_email ON users(email);

Crear índice único (evita valores repetidos):

CREATE UNIQUE INDEX idx_users_unique_email ON users(email);

Borrar índice:

DROP INDEX idx_users_email;

Conviene borrar índices que ya no se usan o que perjudican el rendimiento de escritura.

Ejemplo completo: optimización de un sistema real​

Preparación en DB Browser

Antes de nada, activa las claves foráneas:

PRAGMA foreign_keys = ON;

Creación de tablas users y orders

Vamos a crear un esquema muy típico:

  • users: usuarios con email y fecha de registro
  • orders: pedidos realizados por los usuarios

Creación de users:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL  -- formato YYYY-MM-DD
);

Creación de orders:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  order_date TEXT NOT NULL,  -- formato YYYY-MM-DD
  total REAL NOT NULL,
  status TEXT NOT NULL,      -- por ejemplo: 'pending', 'paid', 'cancelled'
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Con esto ya tienes la estructura básica y la inserción de datos de prueba

Ahora rellenamos las tablas con datos realistas.

Datos de ejemplo para users:

INSERT INTO users (name, email, created_at) VALUES
('Ana López',      'ana@example.com',     '2020-01-15'),
('Luis García',    'luis@example.com',    '2020-03-10'),
('María Sánchez',  'maria@example.com',   '2021-06-05'),
('Carlos Pérez',   'carlos@example.com',  '2021-09-20'),
('Laura Ruiz',     'laura@example.com',   '2022-02-11'),
('Javier Torres',  'javier@example.com',  '2022-05-30'),
('Sofía Martín',   'sofia@example.com',   '2023-01-03'),
('Raúl Fernández', 'raul@example.com',    '2023-03-18');

Ahora metemos pedidos. Supondremos que el id de Ana es 1, el de Luis es 2, etc. (DB Browser asigna ids automáticamente empezando en 1 si la tabla estaba vacía).

Datos de ejemplo para orders:

INSERT INTO orders (user_id, order_date, total, status) VALUES
-- Pedidos de Ana (id 1)
(1, '2023-01-10', 120.50, 'paid'),
(1, '2023-02-14',  89.99, 'paid'),
(1, '2023-03-01',  45.00, 'cancelled'),

-- Pedidos de Luis (id 2)
(2, '2023-01-05', 230.00, 'paid'),
(2, '2023-04-20',  75.20, 'pending'),

-- Pedidos de María (id 3)
(3, '2022-11-11', 310.00, 'paid'),
(3, '2023-02-02', 150.00, 'paid'),

-- Pedidos de Carlos (id 4)
(4, '2021-12-25',  60.00, 'paid'),

-- Pedidos de Laura (id 5)
(5, '2022-07-07', 500.00, 'paid'),
(5, '2023-03-15', 210.00, 'pending'),

-- Pedidos de Javier (id 6)
(6, '2022-09-10',  99.90, 'paid'),

-- Pedidos de Sofía (id 7)
(7, '2023-01-22', 135.00, 'paid'),
(7, '2023-02-05', 180.00, 'paid'),

-- Pedidos de Raúl (id 8)
(8, '2023-03-01',  49.99, 'pending');

Puedes comprobar que todo está cargado con:

SELECT * FROM users;
SELECT * FROM orders;

Consultas típicas antes de crear índices

Imagina consultas como estas:

Buscar un usuario por email:

SELECT *
FROM users
WHERE email = 'maria@example.com';

Buscar todos los pedidos de un usuario concreto:

SELECT *
FROM orders
WHERE user_id = 3
ORDER BY order_date DESC;

Listar pedidos de un email concreto, uniendo users con orders:

SELECT
  u.name,
  u.email,
  o.order_date,
  o.total,
  o.status
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'ana@example.com'
ORDER BY o.order_date DESC;

Con pocos datos, todo va rápido, pero si tuvieras miles de usuarios y cientos de miles de pedidos, aquí es donde los índices marcan la diferencia.

Creación de índices útiles​

Vamos a crear algunos índices razonables para este esquema.

Índice sobre el email de users

Este es muy típico, porque buscar por email es un patrón súper común:

CREATE INDEX idx_users_email ON users(email);

Nota: ya tienes email como UNIQUE en la tabla, y en SQLite eso ya implica un índice interno, así que este índice adicional no sería estrictamente necesario. Pero sirve didácticamente para ver la sintaxis y la idea.

Índice sobre user_id en orders

Muy importante para consultas que buscan pedidos por usuario:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Índice sobre order_date (por ejemplo, para filtrar por rango de fechas)

CREATE INDEX idx_orders_order_date ON orders(order_date);

Consulta que se beneficia de estos índices

Buscar pedidos recientes de un usuario concreto:

SELECT
  o.id,
  o.order_date,
  o.total,
  o.status
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'ana@example.com'
  AND o.order_date >= '2023-01-01'
ORDER BY o.order_date DESC;

Aquí:

  • El índice sobre users.email acelera la búsqueda del usuario.
  • El índice sobre orders.user_id facilita encontrar todos sus pedidos.
  • El índice sobre orders.order_date ayuda con filtros por fechas y ORDER BY, aunque en SQLite el optimizador decide qué usar según el caso.

Cómo ver (un poco) el efecto de los índices con EXPLAIN QUERY PLAN

En DB Browser, puedes anteponer EXPLAIN QUERY PLAN a una consulta para ver de forma sencilla si está usando índices.

Por ejemplo:

EXPLAIN QUERY PLAN
SELECT *
FROM users
WHERE email = 'maria@example.com';

Y:

EXPLAIN QUERY PLAN
SELECT *
FROM orders
WHERE user_id = 3;

En el resultado textual verás referencias a USING INDEX cuando SQLite decide usar el índice.

Esto es opcional, pero ayuda a entender que el índice no es “mágico”, sino una estructura que el motor elige usar si le conviene.

Cuándo crear índices (regla práctica)

En este mini ejemplo, los índices que tienen más sentido son:

  • users(email) si buscas a menudo por email.
  • orders(user_id) si sueles listar pedidos por usuario.
  • orders(order_date) si filtras mucho por rango de fechas o orden cronológico.

Regla rápida:

  • Crea índices en columnas que aparezcan constantemente en:
    • WHERE
    • JOIN
    • ORDER BY
  • No indixes todo:
    • Demasiados índices hacen más lentos los INSERT/UPDATE/DELETE.
    • Cada índice adicional ocupa espacio en disco.

Cómo leer un QUERY PLAN real en SQLite

Interpretación sencilla usando el ejemplo de búsqueda por email

Un query plan es la forma en la que SQLite te muestra cómo va a ejecutar realmente una consulta.

No te devuelve datos, sino la estrategia interna que el motor va a seguir para encontrarlos:

  • ¿Buscará fila a fila toda la tabla?
  • ¿Usará un índice?
  • ¿Usará el índice correctamente o no?
  • ¿Cuántos pasos se necesitan para completar la consulta?

Siempre se lee de arriba hacia abajo y cada línea representa un paso del motor.

Vamos al ejemplo.

Ejemplo real​

EXPLAIN QUERY PLAN
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'ana@example.com';

Caso 1: Sin índices​

Salida típica:

SCAN TABLE users
SEARCH TABLE orders USING INTEGER PRIMARY KEY (rowid=?)

Cómo interpretarlo:​

1. SCAN TABLE users

SQLite recorrerá toda la tabla users para encontrar filas que cumplan email = 'ana@example.com'.

Esto se llama un full table scan.

Es la señal número uno de que necesitas un índice.

2. SEARCH TABLE orders USING INTEGER PRIMARY KEY

Una vez encuentre a Ana, que tiene un id, va a buscar sus pedidos usando la clave primaria de orders.

Esto es bueno, pero la parte costosa está en el primer paso.

Caso 2: Con índices​

Supongamos que has creado:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

Salida típica:

SEARCH TABLE users USING INDEX idx_users_email (email=?)
SEARCH TABLE orders USING INDEX idx_orders_user_id (user_id=?)

Cómo interpretarlo ahora:​

1. SEARCH TABLE users USING INDEX idx_users_email (email=?)

SQLite ya no recorre toda la tabla:

va directamente al índice de emails, encuentra la fila de Ana en muy pocas operaciones.

2. SEARCH TABLE orders USING INDEX idx_orders_user_id (user_id=?)

Para cada usuario encontrado (solo Ana), usa el índice para encontrar sus pedidos.

Sin esto, también tendría que escanear toda la tabla orders.

Conclusión del plan:​

  • Antes: se recorrían tablas completas.
  • Ahora: todo pasa por índices, lo que reduce enormemente el trabajo.

Cómo saber si un QUERY PLAN es bueno​

Reglas rápidas:

Bueno cuando:

  • Ves SEARCH TABLE ... USING INDEX ...
  • No aparecen “SCAN TABLE …” salvo que la tabla sea muy pequeña
  • El índice usado es el esperado (email, user_id, etc.)

Malo cuando:

  • Ves “SCAN TABLE X” en una tabla grande
  • Ves “USING TEMP B-TREE” (SQLite necesita ordenar porque no puede usar un índice)
  • Hay demasiados JOIN sin uso de índices
  • Ves “EXECUTE CORRELATED SUBQUERY” dentro de un loop (puede multiplicar el coste)

Cómo leer un plan en 3 pasos​

  1. Identifica qué tabla se lee primero.
  2. Comprueba si se usa un índice o un SCAN.
  3. Comprueba si los JOIN también usan índices.

Si las primeras líneas muestran “SCAN” sobre una tabla grande, hay margen de mejora.

Optimización basada en patrones comunes en SQLite

Los índices son una de las optimizaciones más importantes en bases de datos.

Pero no se crean al azar, sino en base a patrones de uso. Vamos a ver los patrones uno por uno.

Patrón 1: SELECT … WHERE columna = valor

Búsquedas por igualdad

Es el caso típico: quieres encontrar rápidamente una fila por un valor concreto.

Ejemplo simple:

SELECT *
FROM users
WHERE email = 'ana@example.com';

Sin índice​

SQLite tiene que revisar fila por fila toda la tabla users hasta encontrar el email.

Salida típica del query plan:

SCAN TABLE users

Eso significa: lee toda la tabla completa, aunque solo necesites 1 fila.

Solución​

Crear un índice sobre la columna del filtro:

CREATE INDEX idx_users_email ON users(email);

Con índice​

La consulta se resuelve así:

SEARCH TABLE users USING INDEX idx_users_email (email=?)

Ahora SQLite va directamente al valor ana@example.com, igual que buscar una palabra en un diccionario.

Cuándo usarlo​

  • Si buscas mucho por una columna (email, username, dni, código, slug…).
  • Si la columna es UNIQUE, SQLite ya crea internamente un índice.

Patrón 2: JOIN entre dos tablas por columna FK

Optimizar relaciones

Los JOIN suelen unir tablas por una clave foránea.

Ejemplo real:

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'ana@example.com';

Lo que pasa internamente:

  1. SQLite localiza al usuario por email.
  2. Luego necesita buscar todos sus pedidos, que coinciden con orders.user_id = u.id.

Sin índice en user_id​

El query plan podría mostrar:

SCAN TABLE orders

Significa que, aunque solo necesites los pedidos del usuario 1, SQLite lee toda la tabla orders.

Solución​

Crear índice en la columna FK:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Con índice​

SQLite hace:

SEARCH TABLE users USING INDEX idx_users_email (email=?)
SEARCH TABLE orders USING INDEX idx_orders_user_id (user_id=?)

Es decir:

  • Encuentra al usuario por su email
  • Salta directamente a sus pedidos sin revisar nada más

Regla clave​

Cada vez que una columna sea clave foránea, pregunta: “¿la uso en JOIN o en WHERE?” Si sí → índice recomendado.

Patrón 3: ORDER BY columna LIMIT X

Ordenaciones rápidas

Ejemplo:

SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 5;

Queremos los 5 pedidos más recientes.

Sin índice​

SQLite tiene que: Leer toda la tabla orders, ordenar todas las filas, quedarse con 5

En un query plan, verás:

USE TEMP B-TREE FOR ORDER BY

Eso significa: “he tenido que ordenar manualmente, no tengo un índice que me ayude”.

Solución​

Crear un índice sobre la columna usada para ordenar:

CREATE INDEX idx_orders_date ON orders(order_date);

Con índice​

SQLite ya tiene los datos ordenados internamente.

El orden y el LIMIT se resuelven casi sin esfuerzo.

Regla clave​

Si usas mucho ORDER BY en una columna, crea un índice.

Patrón 4: Búsquedas por rangos (>, <, BETWEEN)

Ejemplo:

SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-01';

Sin índice, SQLite revisa toda la tabla:

SCAN TABLE orders

Porque necesita comparar fila por fila.

Solución​

Índice sobre la columna del rango:

CREATE INDEX idx_orders_order_date ON orders(order_date);

Con índice​

SQLite hace una “búsqueda entre rangos” sobre un árbol B:

SEARCH TABLE orders USING INDEX idx_orders_order_date (order_date>? AND order_date<?)

Esto es extremadamente eficiente, porque los índices están ordenados internamente.

Regla clave​

Siempre indexa columnas usadas en comparaciones de tipo:

  • >
  • <
  • BETWEEN
  • >=
  • <=

Especialmente fechas, cantidades, puntuaciones, etc.

Patrón 5: Muchísimos INSERT

Cuidado con tener demasiados índices. Cada vez que insertas una fila, SQLite no solo la guarda en la tabla, sino que: actualiza todos los índices asociados a la tabla. Esto vuelve más lentas las inserciones.

Ejemplo extremo​

Tabla orders con índices en:

  • user_id
  • order_date
  • status
  • total
  • combinación (user_id, order_date)

Insertar un pedido:

INSERT INTO orders (user_id, order_date, total, status)
VALUES (1, '2023-10-01', 90.00, 'paid');

SQLite tiene que:

  • Añadir la fila a la tabla
  • Actualizar cada uno de los índices

Con demasiados índices, INSERT puede volverse notablemente más lento.

Solución​

Reducir el número de índices.

Reglas:

  1. No indexar columnas que casi nunca usas en WHERE o JOIN.
  2. No indexar columnas con muy poca variedad de valores (por ejemplo, status = 'paid/pending/cancelled'): los índices no ayudan mucho aquí.
  3. Crear índices solo cuando exista una necesidad real de consulta rápida.

Resumen práctico: qué índice crear según el patrón

PatrónÍndice recomendadoEjemplo
SELECT … WHERE columna = valorIndexar esa columnausers(email)
JOIN por columna FKIndexar la FKorders(user_id)
ORDER BY columna LIMIT XIndexar esa columnaorders(order_date)
Rango (>, <, BETWEEN)Indexar esa columnaorders(order_date)
Muchos INSERTReducir índicesEvitar indexar status si no es necesario

Errores comunes que ralentizan consultas​

  • Crear índices en columnas poco discriminantes.
  • Crear demasiados índices en tablas de inserción frecuente.
  • No revisar las consultas en EXPLAIN QUERY PLAN.
  • No crear índices para columnas usadas en JOIN.
  • Mantener índices sin uso tras cambios en la aplicación.
  • Intentar optimizar demasiado pronto. En bases pequeñas, la optimización no se nota.

Buenas prácticas para entornos de producción​

  • Crear índices solo cuando tu aplicación realmente los necesita.
  • Revisar QUERY PLAN periódicamente.
  • Evitar índices innecesarios en tablas de escritura intensiva.
  • Nombrar los índices de forma clara.
  • Revisar índices después de rediseñar tablas.
  • Probar siempre antes y después de crear un índice.

Ejercicio final

Índices y optimización con users y posts

Enunciado​

Vas a crear una base de datos con estas tablas:

users(id, email, name)
posts(id, user_id, created_at, title)

Debes:

  1. Insertar al menos 1000 usuarios y 5000 posts de prueba (pueden ser ficticios).

  2. Ejecutar las siguientes consultas con EXPLAIN QUERY PLAN:

    a. Obtener un usuario por email.

    b. Obtener los posts de un usuario concreto.

    c. Obtener los últimos 20 posts por fecha.

  3. Crear los índices necesarios para optimizar cada consulta.

  4. Volver a ejecutar EXPLAIN QUERY PLAN y comparar resultados.

  5. Justificar cada índice creado y explicar por qué mejora el rendimiento.

A continuación tienes la guía paso a paso para construir todo y entender qué está pasando.

Paso 0​

Crear la base de datos e iniciar el entorno

  1. Abre DB Browser for SQLite.
  2. Crea una base de datos nueva, por ejemplo: blog_indices.db.
  3. Ve a la pestaña Execute SQL.
  4. Ejecuta este comando para activar claves foráneas (buena práctica):
PRAGMA foreign_keys = ON;

Paso 1​

Crear las tablas users y posts

Vamos a crear un esquema sencillo pero realista.

Tabla users:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

Explicación:

  • id: identificador numérico único del usuario.
  • email: correo único; SQLite creará internamente un índice por ser UNIQUE.
  • name: nombre del usuario.

Tabla posts:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  created_at TEXT NOT NULL,  -- formato ISO YYYY-MM-DD
  title TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Explicación:

  • id: identificador del post.
  • user_id: referencia al autor (un usuario).
  • created_at: fecha de creación del post.
  • title: título del post.

Paso 2​

Insertar 1000 usuarios de prueba

En lugar de escribir 1000 INSERT a mano, usaremos una CTE recursiva para generar números del 1 al 1000 y crear usuarios ficticios.

En Execute SQL, ejecuta:

WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000
)
INSERT INTO users (email, name)
SELECT
  'user' || n || '@example.com' AS email,
  'User ' || n AS name
FROM seq;

Qué está pasando:

  • La CTE seq genera los números del 1 al 1000.
  • Cada número n se usa para crear:
    • email: user1@example.com, user2@example.com, …
    • name: User 1, User 2, …
  • Insertamos así 1000 usuarios de prueba de forma automática.

Puedes comprobarlo:

SELECT COUNT(*) AS total_users FROM users;

Deberías obtener 1000.

Paso 3​

Insertar 5000 posts de prueba

Haremos algo parecido: generaremos 5000 números y, para cada uno, crearemos un post.

Usaremos una CTE recursiva y repartiremos los posts entre los usuarios usando una fórmula sencilla.

Ejecuta:

WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5000
)
INSERT INTO posts (user_id, created_at, title)
SELECT
  ((n - 1) % 1000) + 1 AS user_id,  -- reparte los posts entre los 1000 usuarios
  date('2020-01-01', '+' || (n % 365) || ' day') AS created_at,
  'Post número ' || n AS title
FROM seq;

Explicación:

  • seq genera números del 1 al 5000.
  • user_id se asigna como ((n - 1) % 1000) + 1, lo que distribuye los posts de forma uniforme entre los 1000 usuarios.
  • created_at se calcula sumando n % 365 días a la fecha base 2020-01-01. Así tendrás posts repartidos en diferentes fechas del año.
  • title es un texto simple: Post número 1, Post número 2, etc.

Comprueba:

SELECT COUNT(*) AS total_posts FROM posts;

Deberías obtener 5000.

Paso 4​

Consultas sin índices: EXPLAIN QUERY PLAN

Ahora vamos a ejecutar las consultas pedidas y ver cómo las resuelve SQLite antes de optimizar nada.

Consulta 2a​

Obtener un usuario por email

Ejemplo: buscar al usuario con email user500@example.com.

EXPLAIN QUERY PLAN
SELECT *
FROM users
WHERE email = 'user500@example.com';

Posible salida (simplificada):

SCAN TABLE users

Significado:

  • “SCAN TABLE” indica que SQLite recorre la tabla completa de users, fila por fila, hasta encontrar la coincidencia.
  • Con 1000 filas es rápido, pero con cientos de miles sería costoso.

Consulta 2b​

Obtener los posts de un usuario concreto

Supón que quieres todos los posts del usuario con id 500:

EXPLAIN QUERY PLAN
SELECT *
FROM posts
WHERE user_id = 500
ORDER BY created_at DESC;

Posible salida:

SCAN TABLE posts
USE TEMP B-TREE FOR ORDER BY

Significado:

  • “SCAN TABLE posts”: recorre las 5000 filas de posts.
  • “USE TEMP B-TREE FOR ORDER BY”: SQLite tiene que ordenar los resultados en una estructura temporal porque no tiene un índice que le ayude a ordenar por created_at.

Consulta 2c​

Obtener los últimos 20 posts por fecha

EXPLAIN QUERY PLAN
SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 20;

Posible salida:

SCAN TABLE posts
USE TEMP B-TREE FOR ORDER BY

Significado:

  • De nuevo, se escanea toda la tabla.
  • Se ordena todo y al final se aplica el LIMIT 20.
  • Con muchos más datos, esto puede ser caro.

Paso 5​

Crear índices para optimizar cada caso

Ahora vamos a aplicar patrones de optimización.

Patrón 1: SELECT … WHERE columna = valor

→ Índice sobre esa columna.

Patrón 2: WHERE user_id = X

→ Índice sobre la FK user_id.

Patrón 3: ORDER BY created_at LIMIT 20

→ Índice sobre created_at.

SQLite ya tiene internamente un índice sobre users.email por ser UNIQUE, pero vamos a actuar como si no lo supiéramos para reforzar el concepto.

Índice 1​

Optimizar la búsqueda por email

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

Justificación:

  • Muchas consultas buscarán usuarios por email (login, perfiles, etc.).
  • Un índice en email permite localizar un usuario en tiempo logarítmico en lugar de hacer un recorrido completo.

Índice 2​

Optimizar las búsquedas de posts por user_id

CREATE INDEX IF NOT EXISTS idx_posts_user_id ON posts(user_id);

Justificación:

  • Es muy común listar todos los posts de un usuario.
  • user_id se usa en el WHERE y en JOIN futuros.
  • El índice permite saltar directamente a los posts de ese usuario.

Índice 3​

Optimizar los últimos posts por fecha

CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at);

Justificación:

  • created_at se usa para ordenar y filtrar (últimos posts, posts recientes, etc.).
  • Un índice sobre la fecha permite extraer los últimos N registros sin ordenar toda la tabla.

Paso 6​

Volver a ejecutar EXPLAIN QUERY PLAN y comparar

Repetimos las mismas consultas, pero ahora con índices.

6a​

Usuario por email

EXPLAIN QUERY PLAN
SELECT *
FROM users
WHERE email = 'user500@example.com';

Salida esperada (simplificada):

SEARCH TABLE users USING INDEX idx_users_email (email=?)

Interpretación:

  • Ya no hay SCAN.
  • “SEARCH … USING INDEX” indica que SQLite usa el índice para localizar la fila de forma muy eficiente.

6b​

Posts de un usuario concreto

EXPLAIN QUERY PLAN
SELECT *
FROM posts
WHERE user_id = 500
ORDER BY created_at DESC;

Salida esperada (simplificada):

SEARCH TABLE posts USING INDEX idx_posts_user_id (user_id=?)
USE TEMP B-TREE FOR ORDER BY

Interpretación:

  • Ahora, en lugar de leer toda la tabla, SQLite solo lee las entradas que coinciden con user_id = 500.
  • Sigue apareciendo “USE TEMP B-TREE FOR ORDER BY” porque estamos ordenando por created_at y aún no hemos combinado bien los índices para ORDER BY.

Opcionalmente podrías crear un índice compuesto (user_id, created_at), pero eso ya es un nivel más avanzado.

6c​

Últimos 20 posts por fecha

EXPLAIN QUERY PLAN
SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 20;

Salida posible con el índice de fecha:

Según el optimizador, podría seguir usando “USE TEMP B-TREE” o aprovechar el índice. En muchas situaciones, verás algo como:

SCAN TABLE posts USING INDEX idx_posts_created_at

o una forma equivalente indicando uso del índice para ordenar.

Interpretación:

  • SQLite puede usar el índice de created_at porque ya tiene los datos ordenados internamente.
  • Puede avanzar desde el final del índice para obtener los últimos 20 registros sin ordenar toda la tabla.

Paso 7​

Justificación final de cada índice

Índice idx_users_email:

  • Patrón: SELECT … WHERE email = ?.
  • Mejora: evita SCAN TABLE users y permite SEARCH USING INDEX.
  • Efecto: búsquedas por email mucho más rápidas, especialmente con muchos usuarios.

Índice idx_posts_user_id:

  • Patrón: SELECT … FROM posts WHERE user_id = ?.
  • Mejora: en lugar de escanear 5000 posts (o millones), se leen solo los posts de ese usuario.
  • Efecto: listados por usuario mucho más eficientes.

Índice idx_posts_created_at:

  • Patrón: ORDER BY created_at LIMIT X y filtros de rango por fecha.
  • Mejora: evita ordenar toda la tabla o ayuda a acotar rangos rápidamente.
  • Efecto: obtener los últimos posts o posts recientes se vuelve mucho más barato.

Con este ejercicio has practicado:

  • Creación de tablas y datos masivos con CTEs recursivas.
  • Uso de EXPLAIN QUERY PLAN para ver qué hace realmente SQLite.
  • Detección de patrones típicos de consulta.
  • Creación de índices alineados con esos patrones.
  • Comparación de planes antes y después de indexar, con justificación clara.

Ampliación del ejercicio

Vamos a ampliar el ejercicio final con tres bloques más avanzados, pero manteniendo el mismo estilo claro y práctico:

  • Índices compuestos (user_id, created_at)
  • Pruebas con filtros por rango de fechas
  • Ejemplos de “malos índices” y por qué no ayudan

Todo se apoya en la base de datos blog_indices.db con las tablas users y posts que ya creaste.

1. Índices compuestos: user_id, created_at

Hasta ahora hemos creado índices simples:

  • idx_posts_user_id sobre posts(user_id)
  • idx_posts_created_at sobre posts(created_at)

Son útiles, pero en muchas consultas se combinan ambas cosas:

  • filtrar por usuario
  • ordenar por fecha

Por ejemplo:

SELECT *
FROM posts
WHERE user_id = 500
ORDER BY created_at DESC
LIMIT 10;

Esta consulta expresa un patrón muy típico:

  • “Dame los últimos 10 posts del usuario 500”

SQLite podría:

  • usar un índice por user_id para encontrar todos los posts de ese usuario,
  • y luego ordenar por created_at.

Podemos ayudarle aún más con un índice compuesto.

1.1 Crear un índice compuesto​

En Execute SQL:

CREATE INDEX IF NOT EXISTS idx_posts_user_date
ON posts(user_id, created_at);

Qué significa:

  • El índice está ordenado primero por user_id,
  • y dentro de cada user_id, por created_at.

Esto es ideal para consultas que combinan:

  • WHERE user_id = ?
  • con ORDER BY created_at

1.2 Probar el índice compuesto con EXPLAIN QUERY PLAN​

Ejecuta:

EXPLAIN QUERY PLAN
SELECT *
FROM posts
WHERE user_id = 500
ORDER BY created_at DESC
LIMIT 10;

Según el optimizador, puedes ver algo como:

  • uso de idx_posts_user_date
  • y ausencia de “USE TEMP B-TREE FOR ORDER BY”

La idea conceptual es:

  • SQLite puede usar el índice compuesto para localizar todas las filas con user_id = 500,
  • y ya tiene implícitamente el orden por created_at,
  • por lo que no necesita ordenar en memoria.

Incluso puede recorrer el índice en sentido inverso (DESC) para tomar solo los últimos 10.

1.3 ¿Cuándo tiene sentido un índice compuesto?​

Regla práctica:

  • Si muchas consultas combinan varias columnas de la forma:

    WHERE col1 = ? AND col2 …

    WHERE col1 = ? ORDER BY col2 …

    entonces un índice compuesto (col1, col2) suele ser muy útil.

  • En nuestro caso: (user_id, created_at) encaja perfectamente con:

    • “posts de un usuario, ordenados por fecha”.

No tiene sentido crear índices compuestos arbitrarios: siempre deben responder a un patrón concreto de consulta.

2. Pruebas con filtros de rangos de fechas

Las búsquedas por rango son muy frecuentes:

  • posts del último mes
  • posts entre dos fechas
  • informes temporales

Con posts(created_at) e idx_posts_created_at, podemos optimizar este patrón.

2.1 Consultar posts en un rango de fechas​

Ejemplo: posts entre el 1 de marzo de 2020 y el 30 de abril de 2020.

EXPLAIN QUERY PLAN
SELECT *
FROM posts
WHERE created_at BETWEEN '2020-03-01' AND '2020-04-30';

Si el índice idx_posts_created_at está creado, el plan debería mostrar algo parecido a:

  • SEARCH TABLE posts USING INDEX idx_posts_created_at (created_at>? AND created_at<?)

Conceptualmente:

  • SQLite no recorre toda la tabla.
  • Va directamente al primer valor de created_at que cumple el rango,
  • y recorre el índice solo dentro de esos límites.

2.2 Rango por usuario y fecha​

Podemos combinar user_id y created_at con el índice compuesto:

EXPLAIN QUERY PLAN
SELECT *
FROM posts
WHERE user_id = 500
  AND created_at >= '2020-06-01'
  AND created_at <  '2020-09-01'
ORDER BY created_at;

Este patrón es muy poderoso:

  • Busca por usuario
  • Filtra por rango de fecha
  • Ordena por fecha

El índice idx_posts_user_date(user_id, created_at) está diseñado para esto.

Internamente:

  • SQLite localiza el segmento del índice donde user_id = 500,
  • y dentro de ese segmento, aplica el rango de fechas y el orden.

Este tipo de consultas son muy comunes en aplicaciones reales:

historial de actividad, feeds, logs, etc.

3. “Malos índices”: ejemplos y por qué no ayudan

Un “mal índice” no es que esté prohibido, sino que:

  • no se usa en ninguna consulta real,
  • o apenas aporta beneficio,
  • o incluso perjudica el rendimiento en escritura.

Vamos a ver tres casos típicos.

3.1 Índice sobre una columna casi constante​

Imagina que en posts añadimos una columna status:

ALTER TABLE posts ADD COLUMN status TEXT DEFAULT 'published';

Y la mayoría de los posts tienen exactamente el mismo valor: 'published'.

Si haces:

CREATE INDEX idx_posts_status ON posts(status);

¿Ayuda?

  • Si casi todos los valores son iguales, el índice tiene muy poca selectividad:

    buscar status = 'published' devuelve casi todas las filas.

  • SQLite probablemente no ganará mucho rendimiento usando ese índice.

  • Sin embargo, tendrá que mantenerlo actualizado en cada INSERT/UPDATE/DELETE.

Conclusión:

  • Índices sobre columnas con muy pocos valores distintos (por ejemplo, 2 o 3 estados) suelen ser poco útiles.
  • Son más eficaces cuando la columna tiene muchos valores diferentes y el filtro reduce mucho el número de filas.

3.2 Índice sobre columnas que nunca usas en WHERE, JOIN, ORDER BY​

Supón que creas:

CREATE INDEX idx_posts_title ON posts(title);

Esto solo tiene sentido si:

  • haces búsquedas del tipo WHERE title = 'algo'
  • o WHERE title LIKE 'prefix%'
  • o ORDER BY title frecuentemente.

Si tus consultas reales nunca filtran ni ordenan por title, ese índice:

  • casi nunca se usará,
  • pero seguirá costando espacio en disco y tiempo en inserciones.

Conclusión:

  • Antes de crear un índice, piensa:

    “¿esta columna aparece en WHERE/JOINS/ORDER con frecuencia?”

  • Si la respuesta es no, el índice es un candidato a “mal índice”.

3.3 Índices redundantes​

Caso típico:

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

Pregunta: ¿necesito ambos?

  • El índice compuesto (user_id, created_at) ya puede usarse para búsquedas que solo filtran por user_id.
  • En muchos motores, esto hace que el índice simple sobre user_id sea redundante.

En SQLite, el optimizador puede usar cualquiera de los dos, pero mantenemos dos estructuras muy parecidas.

Conclusión:

  • Tener índices redundantes incrementa el coste de escritura y el espacio sin aportar beneficios claros.
  • Es mejor revisar cuáles se usan realmente (EXPLAIN QUERY PLAN y profiler) y eliminar los que sobren.

4. Guía rápida: cuándo un índice es bueno o malo

Índice “bueno” cuando:

  • responde directamente a un patrón de consulta muy repetido
    • WHERE columna = ?
    • JOIN tabla ON fk = pk
    • WHERE fecha BETWEEN ? AND ?
    • WHERE col1 = ? ORDER BY col2
  • tiene buena selectividad (reduce mucho el número de filas que hay que leer)
  • se usa en EXPLAIN QUERY PLAN (USING INDEX …)

Índice “malo” o sospechoso cuando:

  • casi nunca aparece en EXPLAIN QUERY PLAN
  • está en una columna con muy pocos valores distintos
  • está en una columna que nunca usas en filtros ni ordenaciones
  • es redundante con otros índices compuestos