{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Por qué tarda tu consulta\n",
    "\n",
    "Preguntarle a la base qué piensa hacer, y las cuatro cosas que dejan un índice sin usar.\n",
    "\n",
    "Cuaderno de práctica del capítulo 14 de **SQL desde cero**, de Miss Yera.\n",
    "\n",
    "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n",
    "\n",
    "Capítulo completo: https://missyera.com/guias/sql-desde-cero/indices-y-plan/\n",
    "\n",
    "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n",
    "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n",
    "rato antes de abrirlo 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Se baja la base y se deja lista una función `q()` que corre las consultas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import urllib.request\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n",
    "con = sqlite3.connect(\"tienda.db\")\n",
    "\n",
    "def q(sql):\n",
    "    \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n",
    "\n",
    "    Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n",
    "    traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n",
    "    \"\"\"\n",
    "    resultado = None\n",
    "    trozo = \"\"\n",
    "    for linea in sql.splitlines(keepends=True):\n",
    "        trozo += linea\n",
    "        if sqlite3.complete_statement(trozo):\n",
    "            if trozo.strip():\n",
    "                cur = con.execute(trozo.strip())\n",
    "                resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                          columns=[d[0] for d in cur.description])\n",
    "                             if cur.description else None)\n",
    "            trozo = \"\"\n",
    "    if trozo.strip():\n",
    "        cur = con.execute(trozo.strip())\n",
    "        resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                  columns=[d[0] for d in cur.description])\n",
    "                     if cur.description else None)\n",
    "    con.commit()\n",
    "    return resultado\n",
    "\n",
    "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Esta celda baja el ayudante que corrige tus ejercicios. Después, en cada\n",
    "ejercicio que se pueda corregir solo, vas a ver `%%revisa` arriba de la celda:\n",
    "escribe tu respuesta debajo, ejecuta, y te digo si te salió 💛"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "\n",
    "# El ayudante de los cuadernos. Trae la corrección de los ejercicios y, en los\n",
    "# capítulos de consola, la celda mágica que ejecuta los comandos. Se baja en\n",
    "# vez de venir pegado aquí para que siempre sea el último.\n",
    "urllib.request.urlretrieve(\n",
    "    \"https://missyera.com/static/cuadernos/revisa.py\", \"revisa.py\")\n",
    "import revisa\n",
    "revisa.carga({\n",
    "    3: \"aWQgIHBhcmVudCAgbm90dXNlZCAgZGV0YWlsCi0tICAtLS0tLS0gIC0tLS0tLS0gIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCjIgICAwICAgICAgIDAgICAgICAgIFNFQVJDSCBwZWRpZG9zIFVTSU5HIElOVEVHRVIgUFJJTUFSWSBLRVkgKHJvd2lkPT8p\",\n",
    "    4: \"aWQgIHBhcmVudCAgbm90dXNlZCAgZGV0YWlsCi0tICAtLS0tLS0gIC0tLS0tLS0gIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQoyICAgMCAgICAgICAwICAgICAgICBTQ0FOIGNsaWVudGVzIFVTSU5HIENPVkVSSU5HIElOREVYIGlkeF9jbGllbnRlc19jaXVkYWQ=\",\n",
    "    6: \"SW50ZWdyaXR5RXJyb3I6IFVOSVFVRSBjb25zdHJhaW50IGZhaWxlZDogY2xpZW50ZXMubm9tYnJl\",\n",
    "}, lenguaje=\"sql\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Llega el día. La consulta que corrías todas las mañanas en dos segundos hoy\n",
    "tarda cuarenta, o directamente no vuelve. No cambiaste nada: creció la tabla.\n",
    "\n",
    "Este capítulo va de por qué pasa eso y de cómo preguntarle a la base qué\n",
    "piensa hacer *antes* de que lo haga. Porque adivinar por qué una consulta\n",
    "es lenta es perder la tarde, y preguntarle son diez segundos 🔍\n",
    "\n",
    "Y dime: **¿cuál es la consulta que más te hace esperar?** Tenla a mano mientras lees, porque al final del capítulo vas a poder preguntarle a la base por qué tarda, en vez de suponerlo ⚡"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preguntarle a la base qué va a hacer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id, fecha, monto FROM pedidos WHERE canal = 'WhatsApp' AND monto > 1200;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "De esas cuatro columnas la única que importa es `detail`; las otras\n",
    "tres son numeración interna. Y lo que dice es **`SCAN\n",
    "pedidos`**.\n",
    "\n",
    "`SCAN` quiere decir que va a leer **las 900 filas, una por\n",
    "una**, y mirar en cada una si el canal es WhatsApp. Con 900 no lo notas.\n",
    "Con 90 millones, ahí está tu tarde.\n",
    "\n",
    "Es como buscar a alguien en un edificio tocando todas las puertas. Funciona, y\n",
    "no hace falta que te explique por qué no es la mejor idea 🚪"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Un índice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE INDEX idx_pedidos_canal ON pedidos (canal);\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id, fecha, monto FROM pedidos WHERE canal = 'WhatsApp' AND monto > 1200;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`SCAN` se convirtió en **`SEARCH`**. Esa\n",
    "palabra es la que quieres ver.\n",
    "\n",
    "Un índice es exactamente el índice de un libro: una lista ordenada de valores\n",
    "con la página donde está cada uno. La base ya no toca las 900 puertas, va\n",
    "directo a las 231 de WhatsApp.\n",
    "\n",
    "Y el nombre no es decoración: `idx_tabla_columna` se lee solo\n",
    "cuando alguien mire los índices dentro de seis meses."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cuando el orden también cuesta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id FROM pedidos WHERE canal = 'WhatsApp' ORDER BY monto DESC LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dos líneas ahora. La primera es la buena; la segunda dice\n",
    "**`USE TEMP B-TREE FOR ORDER BY`**, y eso significa que\n",
    "la base tuvo que armarse una estructura temporal aparte solo para ordenar. Es\n",
    "trabajo extra que no se ve en el resultado pero sí en el reloj."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE INDEX idx_pedidos_canal_monto ON pedidos (canal, monto);\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id FROM pedidos WHERE canal = 'WhatsApp' ORDER BY monto DESC LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Desapareció el temp b-tree. Un índice sobre `(canal, monto)` ya\n",
    "guarda los montos ordenados dentro de cada canal, así que el\n",
    "`ORDER BY` sale gratis.\n",
    "\n",
    "Y fíjate en la palabra nueva: **`COVERING INDEX`**.\n",
    "Significa que todo lo que la consulta necesitaba estaba en el índice y ni\n",
    "siquiera hizo falta abrir la tabla. Es lo más rápido que se puede pedir 🏎️"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT canal, monto FROM pedidos WHERE canal = 'Web';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Otro covering: pedí `canal` y `monto`, y las dos están\n",
    "en el índice."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La regla del orden de las columnas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un índice sobre `(canal, monto)` sirve para buscar por\n",
    "`canal`, y para buscar por `canal` y `monto`\n",
    "juntos. Pero mira qué pasa buscando solo por la segunda."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id FROM pedidos WHERE monto > 1200;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Volvió el `SCAN`. Lee el índice entero en vez de la tabla entera,\n",
    "que es un poquito mejor, pero sigue siendo leerlo todo.\n",
    "\n",
    "Es una guía telefónica ordenada por apellido y después por nombre: buscar\n",
    "\"Flores\" es instantáneo, buscar \"todos los que se llamen Gera\" es leer la guía\n",
    "completa. **Un índice compuesto sirve desde la primera columna hacia la\n",
    "derecha, nunca al revés**, y eso es igual en los cuatro motores."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Los cuatro sitios donde el índice deja de servir"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esto es lo más útil del capítulo. Tienes el índice, lo ves creado, y la\n",
    "consulta sigue haciendo `SCAN`. Casi siempre es una de estas\n",
    "cuatro."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE INDEX idx_clientes_nombre ON clientes (nombre);\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id FROM clientes WHERE nombre = 'Market Central 087';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese funciona: `SEARCH`. Ahora el mismo con una función encima."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id FROM clientes WHERE UPPER(nombre) = 'MARKET CENTRAL 087';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1. Una función sobre la columna mata el índice.** El índice\n",
    "guarda `Market Central 087`, no `MARKET CENTRAL 087`, así\n",
    "que no puede ir directo. Volvió a `SCAN`: lee el índice entero, igual\n",
    "que en el caso de arriba, y eso es leerlo todo.\n",
    "\n",
    "Y esto duele porque en el capítulo 5 dijimos que `UPPER(columna)`\n",
    "es la forma de buscar igual en los cuatro motores. Las dos cosas son verdad, y\n",
    "por eso hay una tercera 🌟"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id FROM clientes WHERE nombre LIKE '%Central%';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2. Un `LIKE` que empieza por `%` mata el\n",
    "índice.** Y no hay arreglo posible: buscar \"algo que contenga Central\" es\n",
    "como buscar en el índice de un libro las palabras que tengan una \"c\" en el\n",
    "medio. Para eso hacen falta índices de texto completo, que son otro invento."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id FROM clientes WHERE nombre GLOB 'Market*';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En cambio buscar por el principio sí funciona: `SEARCH`, y encima\n",
    "te dice cómo lo hizo, `nombre>? AND nombre<?`. Convirtió el\n",
    "\"empieza por Market\" en un rango.\n",
    "\n",
    "Aquí usé `GLOB` y no `LIKE` a propósito, y es un detalle\n",
    "precioso: el `LIKE` de SQLite no distingue mayúsculas (capítulo 5), y\n",
    "por eso *tampoco* puede usar el índice ni para buscar por el principio.\n",
    "La comodidad se paga. En PostgreSQL, donde `LIKE` sí distingue,\n",
    "`LIKE 'Market%'` usa el índice sin problema.\n",
    "\n",
    "Y ahora la solución al problema del `UPPER`: si vas a buscar\n",
    "siempre en mayúsculas, indexa las mayúsculas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE INDEX idx_upper ON clientes (UPPER(nombre));\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT id FROM clientes WHERE UPPER(nombre) = 'MARKET CENTRAL 087';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`SEARCH clientes USING INDEX idx_upper`. Un índice puede estar\n",
    "hecho sobre una expresión y no solo sobre una columna, y eso resuelve el choque\n",
    "entre \"escribo igual para los cuatro motores\" y \"quiero que sea rápido\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Índice sobre una expresión\n",
    "\n",
    "| PostgreSQL | `CREATE INDEX i ON clientes (UPPER(nombre))` |\n",
    "|---|---|\n",
    "| MySQL | `desde la 8.0.13, y con paréntesis dobles: ((UPPER(nombre)))` |\n",
    "| SQL Server | `no directamente: se crea una columna calculada persistida y se indexa esa` |\n",
    "| SQLite | `CREATE INDEX i ON clientes (UPPER(nombre))   -- desde la 3.9` |\n",
    "\n",
    "PostgreSQL y SQLite igual otra vez, MySQL con una sintaxis rara que se olvida, y SQL Server obligando a añadir una columna. Es de las diferencias que más cambian cómo diseñas la tabla."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La tercera y la cuarta no se ven bien en SQLite, así que van dichas:\n",
    "\n",
    "**3. Comparar una columna con otro tipo.** Si\n",
    "`id_cliente` es un número y escribes\n",
    "`WHERE id_cliente = '58'` entre comillas, algunos motores convierten\n",
    "la columna entera y ahí se fue el índice. En MySQL pasa muchísimo.\n",
    "\n",
    "**4. Un `OR` entre columnas distintas.**\n",
    "`WHERE canal = 'Web' OR ciudad = 'Lima'` no puede usar un solo índice\n",
    "para las dos. A veces se arregla partiéndolo en dos consultas con\n",
    "`UNION`, que es del capítulo 8."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Los índices y los JOIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT c.nombre, p.monto\n",
    "FROM clientes c JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE c.ciudad = 'Lima';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lee así: recorre `pedidos` entera y por cada fila va a buscar su\n",
    "cliente por la clave primaria. O sea 900 búsquedas. Funciona porque\n",
    "`clientes.id` es `PRIMARY KEY` y por tanto ya tiene índice\n",
    "sin que nadie lo pida."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "CREATE INDEX idx_pedidos_cliente ON pedidos (id_cliente);\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT c.nombre, p.monto\n",
    "FROM clientes c JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE c.ciudad = 'Lima';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se dio vuelta el plan: ahora recorre `clientes` (que son 120) y\n",
    "por cada uno busca sus pedidos con el índice nuevo. 120 búsquedas en vez de\n",
    "900.\n",
    "\n",
    "Y de aquí sale la regla que más rendimiento regala por menos trabajo:\n",
    "**indexa siempre las columnas de clave foránea**. La clave primaria\n",
    "la indexa el motor solo; la foránea, no (salvo en MySQL con InnoDB, que es el\n",
    "único de los cuatro que lo hace por su cuenta) 🔑"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que un índice no arregla"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT STRFTIME('%Y-%m', fecha) AS mes, COUNT(*) FROM pedidos GROUP BY mes;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`SCAN` y `TEMP B-TREE FOR GROUP BY`. Y está bien: si\n",
    "tienes que contar todos los pedidos de todos los meses, hay que leerlos todos,\n",
    "con índice o sin él.\n",
    "\n",
    "**Un índice sirve para encontrar pocas filas entre muchas.** Si\n",
    "la consulta necesita casi todas, leer la tabla entera es lo más rápido que hay y\n",
    "el motor lo sabe. Por eso a veces creas un índice, el plan no cambia, y el motor\n",
    "tiene razón 🤷‍♀️"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que cuesta un índice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' AND sql IS NOT NULL ORDER BY name;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cinco índices llevamos. Y aquí va el aviso, porque a todo el mundo le pasa:\n",
    "la primera reacción cuando algo va lento es crear índices, y cada índice\n",
    "**ocupa espacio y hace más lento cada INSERT, UPDATE y DELETE**,\n",
    "porque hay que mantenerlos todos al día.\n",
    "\n",
    "Una tabla que se escribe mucho y se consulta poco no quiere índices. Una que\n",
    "se consulta mucho y se escribe de noche, sí. Y los que no usa nadie son solo\n",
    "peso muerto."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "DROP INDEX idx_pedidos_canal;\n",
    "SELECT COUNT(*) AS indices FROM sqlite_master WHERE type = 'index' AND sql IS NOT NULL;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Borré `idx_pedidos_canal` porque el de `(canal, monto)`\n",
    "ya hace su trabajo: un índice compuesto sirve también para buscar solo por su\n",
    "primera columna, así que el de una sola sobraba.\n",
    "\n",
    "Un índice también puede ser `UNIQUE`, y ahí deja de ser solo\n",
    "velocidad y pasa a ser una promesa, como las del capítulo 10."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    CREATE UNIQUE INDEX idx_producto_unico ON productos (nombre);\n",
    "    INSERT INTO productos (id, nombre, categoria, precio) VALUES (41, 'Producto 01', 'Snacks', 10);\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "IntegrityError: UNIQUE constraint failed: productos.nombre\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y uno mal escrito falla al crearse, que es la mejor forma de fallar."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    CREATE INDEX idx_malo ON pedidos (id_no_existe);\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "OperationalError: no such column: id_no_existe\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Las estadísticas, que es de lo que vive el planificador"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "ANALYZE;\n",
    "SELECT COUNT(*) AS filas_de_estadisticas FROM sqlite_stat1;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El motor no elige el plan al azar: estima cuántas filas va a devolver cada\n",
    "paso y elige el camino más barato. Esa estimación sale de unas estadísticas que\n",
    "hay que mantener al día.\n",
    "\n",
    "Si cargaste un millón de filas de golpe y las consultas se pusieron raras, esto\n",
    "es lo primero que hay que probar: el motor sigue creyendo que la tabla tiene\n",
    "cien filas y está eligiendo planes para cien filas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ver el plan de una consulta\n",
    "\n",
    "| PostgreSQL | `EXPLAIN, y EXPLAIN ANALYZE que además la ejecuta y mide de verdad` |\n",
    "|---|---|\n",
    "| MySQL | `EXPLAIN, y EXPLAIN ANALYZE desde la 8.0.18` |\n",
    "| SQL Server | `SET SHOWPLAN_ALL ON, o el botón de plan de ejecución en SSMS` |\n",
    "| SQLite | `EXPLAIN QUERY PLAN` |\n",
    "\n",
    "EXPLAIN a secas te dice qué PIENSA hacer; EXPLAIN ANALYZE lo ejecuta y te dice qué hizo y cuánto tardó de verdad. Cuando la estimación y la realidad no se parecen, ahí está el problema, y casi siempre son estadísticas viejas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cómo se llama \"leer la tabla entera\" en cada plan\n",
    "\n",
    "| PostgreSQL | `Seq Scan` |\n",
    "|---|---|\n",
    "| MySQL | `type: ALL` |\n",
    "| SQL Server | `Table Scan o Clustered Index Scan` |\n",
    "| SQLite | `SCAN` |\n",
    "\n",
    "Cuatro nombres para la misma mala noticia. Si aprendes a reconocer estos cuatro y su contrario (Index Scan, type: ref, Index Seek, SEARCH), ya sabes leer un plan en cualquiera de los cuatro."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Actualizar las estadísticas\n",
    "\n",
    "| PostgreSQL | `ANALYZE   -- y el autovacuum lo hace solo` |\n",
    "|---|---|\n",
    "| MySQL | `ANALYZE TABLE pedidos` |\n",
    "| SQL Server | `UPDATE STATISTICS pedidos   -- también automático` |\n",
    "| SQLite | `ANALYZE` |\n",
    "\n",
    "Tres se llaman parecido y SQL Server otra vez con lo suyo. En PostgreSQL y SQL Server suele estar automatizado; en MySQL y SQLite, después de una carga grande, vale la pena lanzarlo a mano."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Índices parciales, o sea con WHERE\n",
    "\n",
    "| PostgreSQL | `CREATE INDEX i ON pedidos (fecha) WHERE monto > 1000` |\n",
    "|---|---|\n",
    "| MySQL | `no los tiene` |\n",
    "| SQL Server | `CREATE INDEX i ON pedidos (fecha) WHERE monto > 1000   -- filtered index` |\n",
    "| SQLite | `CREATE INDEX i ON pedidos (fecha) WHERE monto > 1000` |\n",
    "\n",
    "Sirven un montón cuando solo consultas una parte de la tabla: el índice ocupa mucho menos y se mantiene más rápido. MySQL es el único que se queda fuera."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Siete sobre tu copia de `tienda.db`. Intenta antes de abrir 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Antes y después\n",
    "\n",
    "Mira el plan de una búsqueda por ciudad, crea el índice y\n",
    "míralo otra vez."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. El índice compuesto y su orden\n",
    "\n",
    "Crea un índice sobre `(ciudad, segmento)` y\n",
    "comprueba con qué consultas sirve y con cuáles no."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. La clave primaria ya viene con índice\n",
    "\n",
    "Mira el plan de buscar un pedido por su id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 3\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. El índice que no se usa\n",
    "\n",
    "Con el índice de ciudad ya creado, comprueba qué pasa si\n",
    "buscas con una función encima."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 4\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Indexa la clave foránea\n",
    "\n",
    "Mira el plan de juntar detalle con productos, crea el índice\n",
    "que falta y compara."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Un índice que promete\n",
    "\n",
    "Impide con un índice que dos clientes tengan el mismo\n",
    "nombre exacto."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 6\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Escríbelo para los cuatro\n",
    "\n",
    "Sin ejecutar: el mismo índice y la misma pregunta por el\n",
    "plan, en los cuatro motores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El índice que creaste y nadie usa"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y con el plan ya en la mano, la trampa del capítulo. Es la razón número uno por la que un índice nuevo no cambia nada 🐌"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Creas el índice sobre la fecha porque el reporte mensual tarda. Vuelves a correrlo y tarda igual.\n",
    "\n",
    "```\n",
    "CREATE INDEX ix_fecha ON pedidos(fecha);\n",
    "\n",
    "EXPLAIN QUERY PLAN\n",
    "SELECT COUNT(*) FROM pedidos\n",
    "WHERE substr(fecha, 1, 7) = '2025-01';\n",
    "-- SCAN pedidos USING COVERING INDEX ix_fecha\n",
    "```\n",
    "\n",
    "**¿Qué está mal?** La respuesta está en el cuaderno de soluciones. Míralo tú primero."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Tienes un índice en la columna fecha y la consulta sigue tardando. Filtras con WHERE strftime('%Y', fecha) = '2026'.\n",
    "\n",
    "a) Al envolver la columna en una función, el índice deja de servir\n",
    "\n",
    "b) El índice está mal creado y hay que rehacerlo\n",
    "\n",
    "c) Hacen falta más índices en esa tabla\n",
    "\n",
    "d) La tabla es demasiado grande para que un índice ayude"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 🔍 Pregúntale el plan antes de adivinar. En SQLite es\n",
    "`EXPLAIN QUERY PLAN` y la única columna que importa es\n",
    "`detail`.\n",
    "\n",
    "- 🚪 `SCAN` es leer todo, `SEARCH` es ir directo. Los\n",
    "cuatro motores lo llaman distinto y significan lo mismo.\n",
    "\n",
    "- 📚 Un índice compuesto sirve de izquierda a derecha: `(canal,\n",
    "monto)` vale para `canal`, no para `monto` solo.\n",
    "\n",
    "- 🏎️ `COVERING INDEX` es cuando todo lo que pediste estaba en el\n",
    "índice y no hizo falta abrir la tabla.\n",
    "\n",
    "- 💀 Lo que mata un índice: una función sobre la columna, un\n",
    "`LIKE '%algo%'`, comparar con otro tipo y un `OR` entre\n",
    "columnas distintas.\n",
    "\n",
    "- 🔑 Indexa las claves foráneas. La primaria la indexa el motor solo; la\n",
    "foránea solo MySQL con InnoDB.\n",
    "\n",
    "- ⚖️ Cada índice hace más lenta cada escritura. Los que no usa nadie son peso\n",
    "muerto.\n",
    "\n",
    "- 📊 Si los planes se pusieron raros después de una carga grande, actualiza las\n",
    "estadísticas.\n",
    "\n",
    "Y si de todo el capítulo te llevas una sola frase, que sea esta:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Crear un índice es fácil. Comprobar que se usa son diez segundos, y es lo\n",
    "único que cambia algo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y si la consulta va lenta porque estás trayendo más datos de los que el\n",
    "reporte necesita, a veces el arreglo no es un índice: es que ese cálculo\n",
    "debía vivir en el tablero. La\n",
    "[guía de Power BI](https://missyera.com/guias/power-bi-desde-cero/) lo cuenta desde\n",
    "ese lado ⚡\n",
    "\n",
    "En el capítulo 14 vienen las vistas, los procedimientos y los disparadores, o\n",
    "sea las cosas que viven guardadas dentro de la base en vez de en tu archivo de\n",
    "consultas.\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 14 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/indices-y-plan/\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}