EXPLAIN: el plan de la consulta
En resumen: EXPLAIN muestra el plan de la consulta, es decir, cómo la ejecutará la base de datos: si lee toda la tabla o busca por un índice (en SQLite, EXPLAIN QUERY PLAN dice SCAN o SEARCH).
Problema
«El informe es lento»
Un compañero dice que el informe de las visitas de un cliente es lento. Podrías adivinar: quizá la tabla es grande, quizá la condición es complicada, quizá falta un índice. O puedes preguntarle a la base de datos cómo piensa ejecutarlo exactamente.
Toda base de datos hace primero un plan de consulta: por qué tabla empezar, si leer todo o buscar por un índice, si ordenar. Solo después lo ejecuta. El plan se puede ver.
La idea
EXPLAIN QUERY PLAN
En SQLite escribes EXPLAIN QUERY PLAN delante de la consulta. La consulta no se ejecuta. En lugar de un resultado obtienes el plan:
La columna que importa es detail. id y parent ordenan los pasos en un árbol cuando hay varios, y notused no se usa. Aquí puedes ignorarlas. SCAN visits significa: leer toda la tabla fila por fila.
En este campo el índice sobre customer_id ya está creado:
SEARCH significa una búsqueda por un índice. Los paréntesis dicen qué busca: customer_id=?, es decir, un valor de customer_id. SCAN o SEARCH es lo primero que miras en un plan.
Pruébalo
«Mis pedidos»
La página «Mis pedidos» busca los pedidos de un cliente por customer_id. Mira el plan en el campo y crea después un índice idx_orders_customer para que el plan de la misma consulta pase a ser SEARCH.
Solución
CREATE INDEX idx_orders_customer ON orders(customer_id);CREATE INDEX idx_orders_customer ON orders(customer_id); La comprobación ejecuta el plan de SELECT * FROM orders WHERE customer_id = 5 después de tu respuesta y lo compara con el plan tras la solución, por eso el nombre del índice tiene que ser el mismo. Fíjate en que customer_id es una clave foránea y no tenía índice. SQLite y PostgreSQL no crean un índice así por su cuenta. MySQL (InnoDB) sí.
La idea
Índice que cubre
Compara el plan de SELECT * de arriba con el plan de COUNT(*) con la misma condición:
Aquí dice USING COVERING INDEX. El índice guarda el valor de customer_id y el número de fila. Para COUNT(*) eso basta: la base de datos cuenta las entradas del índice y no abre la tabla. Esto se llama índice que cubre (covering index).
SELECT * necesita también product_id y seen_at. No están en el índice, así que por cada entrada encontrada la base de datos va además a la tabla. El número de fila (id) está en cada entrada del índice, por eso SELECT id con la misma condición queda cubierto:
Míralo
La ordenación oculta
La página muestra las visitas del cliente empezando por la más nueva. Ejecuta el plan:
La segunda línea es USE TEMP B-TREE FOR ORDER BY. El índice ayudó a encontrar las filas, pero no están ordenadas por seen_at. Así que la base de datos las ordena ella misma, en una estructura temporal. Con 60 filas no es nada. Con 60 000 empiezas a notarlo. En la próxima lección haremos un índice que da las filas ya ordenadas.
Una búsqueda por clave primaria tiene su propia entrada en el plan:
La idea
Cómo descubrir por qué es lento
Ejecuta el plan de la consulta lenta y busca:
- SCAN de una tabla grande mientras la condición elige pocas filas. Falta un índice, o la condición no puede usarlo (esa es la última lección del módulo).
- USE TEMP B-TREE. La base de datos ordena o agrupa por sí misma. Un índice en el orden correcto puede evitarlo.
- SEARCH, pero sigue lento. La condición elige muchas filas, o el índice no cubre la consulta y se lee la tabla por cada fila.
El plan dice lo que va a pasar. Cuánto tarda, lo ves al ejecutar la consulta misma.
Más precisamente, para curiosos
En SQLite, EXPLAIN sin QUERY PLAN muestra el programa en el que SQLite convierte la consulta (instrucciones para su máquina virtual). Eso es para quienes trabajan en el propio SQLite.
En PostgreSQL, EXPLAIN muestra el plan con estimaciones: cuántas filas espera cada paso y cuánto «costará». EXPLAIN ANALYZE ejecuta de verdad la consulta y añade el tiempo real y el número real de filas de cada paso. Cuando la estimación y la realidad difieren mucho, ahí suele estar el origen de un plan lento. Allí una lectura completa se llama Seq Scan, una búsqueda por índice Index Scan y un índice que cubre Index Only Scan. Ojo: EXPLAIN ANALYZE sobre un UPDATE o un DELETE cambia los datos de verdad, así que ejecútalo dentro de una transacción y haz ROLLBACK después.
MySQL tiene EXPLAIN, y desde la versión 8.0.18 también EXPLAIN ANALYZE.
Ejercicio
Dos tareas
1. El informe de octubre cuenta las visitas a partir del 1 de octubre de 2026. Crea un índice idx_visits_seen para que el plan sea SEARCH.
Un índice que convierte el plan del informe de octubre en SEARCH:
Solución
CREATE INDEX idx_visits_seen ON visits(seen_at);CREATE INDEX idx_visits_seen ON visits(seen_at); El plan pasa a ser SEARCH visits USING COVERING INDEX idx_visits_seen (seen_at>?). El índice está ordenado, así que con >= la base de datos encuentra la primera fecha y sigue leyendo hasta el final. Y cubre la consulta, porque COUNT(*) no necesita nada de la tabla.
2. El cliente 4321 pide que se borren sus datos. Primero el programa solo necesita los números de sus visitas. El índice sobre customer_id ya está creado. Escribe la consulta de modo que la base de datos no lea la tabla.
Los números (id) de las visitas del cliente 4321, solo desde el índice:
Solución
SELECT id FROM visits WHERE customer_id = 4321;SELECT id FROM visits WHERE customer_id = 4321; 60 visitas. Compruébalo con EXPLAIN QUERY PLAN delante de la consulta: dice USING COVERING INDEX. Con SELECT * el resultado sería distinto, y la base de datos abriría también cada una de las 60 filas.
Test