Indexes

The Idea

An index is a separate structure that keeps one or more columns in sorted order, each entry pointing back at its row. It turns "read everything and check" into "jump straight there". The price is paid on writes: every insert and update must maintain the index too.

Real-World Example

A newspaper photo library files prints in boxes by date. A second drawer holds cards sorted by photographer, each naming a box number. Finding every Weiss print takes seconds instead of an afternoon — but each new print now means filing twice, and nobody indexes by every field.

The Code

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE photo (id INTEGER, shot_by TEXT, box INTEGER);
  INSERT INTO photo VALUES (1, 'Okonjo', 4), (2, 'Weiss', 9), (3, 'Okonjo', 2);
""")
q = "SELECT box FROM photo WHERE shot_by = 'Okonjo'"
print(db.execute("EXPLAIN QUERY PLAN " + q).fetchone()[3])
db.execute("CREATE INDEX photo_by ON photo(shot_by)")
print(db.execute("EXPLAIN QUERY PLAN " + q).fetchone()[3])
# SCAN photo
# SEARCH photo USING INDEX photo_by (shot_by=?)

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE photo (id INTEGER, shot_by TEXT, box INTEGER);
INSERT INTO photo VALUES (1, 'Okonjo', 4), (2, 'Weiss', 9);
""")
# Make this lookup a SEARCH instead of a SCAN.
db.execute("CREATE INDEX photo_by ON photo(___)")
plan = db.execute("EXPLAIN QUERY PLAN "
                "SELECT box FROM photo WHERE shot_by = 'Weiss'").fetchone()
print(plan[3])   # SEARCH photo USING INDEX photo_by (shot_by=?)