Query Optimization: Indexing & EXPLAIN QUERY PLAN
Without an index, the database performs a full table scan ($O(n)$). Adding a B-Tree index (`CREATE INDEX`) drops lookups to $O(\log n)$. Use `EXPLAIN QUERY PLAN` to verify index utilization 🔍.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE accounts (id INT, email TEXT)")
conn.execute("CREATE INDEX idx_email ON accounts (email)")
# Inspect query execution strategy:
plan = conn.execute("EXPLAIN QUERY PLAN SELECT * FROM accounts WHERE email = 'a@b.com'").fetchall()
for step in plan:
print(step) # Shows 'SEARCH accounts USING INDEX idx_email' 🔍
conn.close()
# 29. Query Optimization: Indexing & EXPLAIN QUERY PLAN Without an index, the database performs a full table scan ($O(n)$). Adding a B-Tree index (`CREATE INDEX`) drops lookups to $O(\log n)$. Use `EXPLAIN QUERY PLAN` to verify index utilization 🔍. ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE accounts (id INT, email TEXT)") conn.execute("CREATE INDEX idx_email ON accounts (email)") # Inspect query execution strategy: plan = conn.execute("EXPLAIN QUERY PLAN SELECT * FROM accounts WHERE email = 'a@b.com'").fetchall() for step in plan: print(step) # Shows 'SEARCH accounts USING INDEX idx_email' 🔍 conn.close() ``` Never guess why a query is slow. Run `EXPLAIN QUERY PLAN`! If you see `SCAN TABLE` instead of `SEARCH TABLE USING INDEX`, you've identified your missing index! 🔍 --- ### Question What output in `EXPLAIN QUERY PLAN` indicates a disastrously unindexed query on large tables? 🧠 - [ ] a. `SCAN TABLE ...` (meaning the database must examine every single row sequentially) 🔍 - [ ] b. `SEARCH TABLE ... USING INDEX` - [ ] c. `PARALLEL WORKER JOIN` - [ ] d. `IN-MEMORY CACHE HIT` --- ### Challenge Create table `users (uid INT, handle TEXT)`. Create an index on `handle`. Run `EXPLAIN QUERY PLAN SELECT * FROM users WHERE handle = 'ada'`. Print whether `'USING INDEX'` appears in the plan description. 🎯 <details> <summary>Solution</summary> ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE users (uid INT, handle TEXT)") conn.execute("CREATE INDEX idx_handle ON users (handle)") plan = conn.execute("EXPLAIN QUERY PLAN SELECT * FROM users WHERE handle = 'ada'").fetchall() detail = plan[0][3] print("USING INDEX" in detail) conn.close() ``` </details>