Index vs Full Table Scan
Try it: Index vs Full Table Scan
Why an index speeds up a lookup: without one the database must scan and test every row; with CREATE INDEX it binary-searches a sorted list of (key, rowid) entries and reads only the matching rows.
How it works
- No index on the searched column: SCAN the table, testing every row.
- CREATE INDEX keeps (key, rowid) entries sorted by key in a separate B-tree.
- A lookup binary-searches the entries for the first key ≥ the target, halving the range each probe.
- It then walks forward while keys still qualify, fetching each matching row by its rowid, and stops at the first key past the target — sorted order means nothing later can match.
Default run (11 steps): SELECT * FROM people WHERE age = 27. Query plan: SEARCH people USING INDEX idx_people_age (age=?). … 3 matching rows, returned in index order. Read 3 table rows + 8 index entries (a full scan reads 12).
Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). The B-tree is drawn as its sorted leaf level, and binary search stands in for descending the tree. Counts are rows and entries touched, not disk pages. The query plan text is what SQLite's EXPLAIN QUERY PLAN reports for the same query.
Loading the simulation…