{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "94089dac-e97a-43a0-8133-6bb42af687bd",
   "metadata": {},
   "source": [
    "# Indexing example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a07482f-daf4-43e4-8714-df2862a5dd5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection, df_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a45081b-11ec-464f-90d3-9636cb57f868",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = db_connection(config_file = 'titanic.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6de5b92b-33c6-494c-81b1-9933693b1cd0",
   "metadata": {},
   "source": [
    "### Remove an age index if one exists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9efb2adf-be1b-4b81-9f95-c5fa0faf917a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from mysql.connector import Error\n",
    "\n",
    "try:\n",
    "    cursor.execute(\"DROP INDEX age_index ON passengers\")\n",
    "except Error as e:\n",
    "    print(f'DROP INDEX failed: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5eae85c-b138-47c4-b6c8-f80b287b8e1f",
   "metadata": {},
   "source": [
    "### The example query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9df29fee-2c34-4465-a162-a4568df913f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT *\n",
    "    FROM passengers\n",
    "    WHERE age IN (45.5, 55.5, 60.5, 75.5, 80)\n",
    "    ORDER BY age\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5f09f4f-012f-4dd2-813d-7dd27a24c309",
   "metadata": {},
   "source": [
    "### Without indexing, how many rows were scanned?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3126d6f1-d640-4610-a6fc-08bc1e1ab645",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    EXPLAIN SELECT *\n",
    "    FROM passengers\n",
    "    WHERE age IN (45.5, 55.5, 60.5, 75.5, 80)\n",
    "    ORDER BY age\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "890a68d5-6012-449c-8053-b2e4625dc9e0",
   "metadata": {},
   "source": [
    "### Create an index on age."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7536e416-f52b-4f67-9ff0-0ef9cf17b24b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"CREATE INDEX age_index ON passengers(age)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f503c215-4111-4574-86e9-13de41edf7b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT *\n",
    "    FROM passengers\n",
    "    WHERE age IN (45.5, 55.5, 60.5, 75.5, 80)\n",
    "    ORDER BY age\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a7fa781-56a1-4801-9bc5-c556b5e27e2b",
   "metadata": {},
   "source": [
    "### With indexing, how many rows were scanned?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54d80ed4-0113-467b-b0e8-9adba861c8f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    EXPLAIN SELECT *\n",
    "    FROM passengers\n",
    "    WHERE age IN (45.5, 55.5, 60.5, 75.5, 80)\n",
    "    ORDER BY age\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84d469cc-7b05-4955-be60-aecd7191dc6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d662b883-2121-407c-a16e-d4a83739f54b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}