SQL desde Python

Optimización de consultas

Con 2000 pedidos, cualquier consulta de PyShop responde en un instante. El problema aparece después: cuando la tabla crece, cuando una consulta se repite miles de veces o cuando un programa escribe fila por fila. Optimizar no es adivinar; es medir: preguntarle a SQLite cómo piensa ejecutar la consulta, cambiar una sola cosa y volver a medir. Esta lectura recorre las herramientas básicas: el plan de ejecución, los índices, las columnas que pides, las escrituras en lote y el problema N+1. Todo está en el notebook optimizacion-sqlite.

1. Leer el plan: EXPLAIN QUERY PLAN

Si antepones EXPLAIN QUERY PLAN a una consulta, SQLite no la ejecuta: devuelve el plan. La cuarta columna de cada fila describe un paso. Las dos palabras que importan son SCAN (recorre todas las filas de la tabla) y SEARCH (visita solo un subconjunto, gracias a un índice o a la clave primaria).

def plan(con, sql, params=()):
    filas = con.execute("EXPLAIN QUERY PLAN " + sql, params)
    return [f[3] for f in filas]

consulta = "SELECT * FROM pedidos WHERE cliente_id = 42"
print(plan(con, consulta))   # ['SCAN pedidos']

La documentación de SQLite advierte que este texto es para depurar y puede cambiar entre versiones: léelo, pero no escribas programas que dependan de su formato exacto.

2. Índices: de SCAN a SEARCH

Un índice es una estructura ordenada aparte que guarda los valores de una columna y dónde está cada fila, como el índice alfabético de un libro. Para buscar los 10 pedidos del cliente 42 sin índice, SQLite revisa los 2000; con índice, va directo a ellos.

con.execute(
    "CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id)")
print(plan(con, consulta))
# ['SEARCH pedidos USING INDEX idx_pedidos_cliente (cliente_id=?)']

Al repetir esa consulta 2000 veces, la versión con índice fue unas 7 veces más rápida en la máquina donde se preparó el curso. En la tuya la proporción puede variar; el cambio de SCAN a SEARCH, no. Las claves primarias ya tienen índice: en un JOIN entre detalle y productos, el plan muestra SCAN d y luego SEARCH p USING INTEGER PRIMARY KEY. Las columnas UNIQUE también tienen un índice automático.

3. Cuándo el índice no sirve

Una función sobre la columna. El índice de fecha guarda la fecha tal cual. La condición substr(fecha, 1, 7) = '2025-03' transforma la columna, y SQLite vuelve a hacer SCAN. Reescrita como rango, fecha BETWEEN '2025-03-01' AND '2025-03-31', usa el índice (SEARCH) y devuelve los mismos 155 pedidos.

LIKE. nombre tiene índice por ser UNIQUE, pero nombre LIKE 'Libro%' hace SCAN. La razón: en SQLite, LIKE no distingue mayúsculas de minúsculas y ese índice sí. La optimización de LIKE solo ocurre si ambos coinciden, por ejemplo con un índice COLLATE NOCASE, y si el patrón no empieza con %. En el notebook, tras crear ese índice, LIKE 'Libro%' pasa a SEARCH y LIKE '%Python' sigue en SCAN.

Los índices tienen costo: ocupan espacio y cada INSERT, UPDATE o DELETE debe actualizarlos. Crea los que piden tus consultas frecuentes y comprueba con el plan que se usan.

4. Pide solo lo que necesitas

SELECT * trae todas las columnas aunque uses una. Además de mover menos datos, pedir solo columnas que están en el índice permite que el índice cubra la consulta: SELECT cliente_id, COUNT(*) FROM pedidos WHERE cliente_id = 42 aparece como SEARCH pedidos USING COVERING INDEX, y SQLite responde sin leer la tabla.

5. Escribe en lote

Cada commit() en una base en archivo obliga a SQLite a asegurar los datos en el disco, y eso es lento. Insertar 2000 filas con un commit por fila tardó unos 36 segundos en la máquina de referencia; con executemany dentro de una sola transacción, una centésima de segundo: miles de veces menos. La proporción depende mucho del disco, pero la regla no cambia:

filas = [(i, f"fila {i}") for i in range(2000)]
with con:   # una sola transacción
    con.executemany("INSERT INTO x VALUES (?, ?)", filas)

El notebook mide la diferencia con 200 filas para que la celda no tarde.

6. El problema N+1

Para mostrar 200 pedidos con sus artículos, el camino ingenuo hace 1 consulta para los pedidos y otra por cada pedido para su detalle: 201 consultas. Un JOIN trae exactamente las mismas líneas en una sola:

SELECT d.pedido_id, d.producto_id, d.cantidad
FROM (SELECT id FROM pedidos ORDER BY id LIMIT 200) AS pe
JOIN detalle AS d ON d.pedido_id = pe.id;

En SQLite cada consulta extra es barata porque no hay red; contra un servidor remoto, cada una suma un viaje de ida y vuelta, y N+1 se vuelve el cuello de botella más común en aplicaciones.

Trampas comunes

• Optimizar sin medir. Mira el plan antes y después de cada cambio.
• Indexar todo. Cada índice hace más lentas las escrituras; crea solo los que se usan.
• Funciones sobre columnas indexadas. Reescribe la condición como rango sobre la columna original.
• Un commit por fila. Agrupa las escrituras relacionadas en una transacción.
• Consultas dentro de un bucle. Casi siempre se pueden reemplazar por un JOIN o un IN.

En el notebook, escribe usa_indice(con, sql) y úsala para revisar cada consulta que reescribas: es la costumbre que más tiempo ahorra.