Database Indexing Simulator
Learn how database indexes work with an interactive simulator. See the difference between full table scans and index seeks, understand B-tree structure, and learn when to create indexes.
Category: Database
Topics covered: database, sql, performance, indexing, educational, backend
// simulator
Database Indexing Simulator
Learn how database indexes work with an interactive simulator. See the difference between full table scans and index seeks, understand B-tree structure, and learn when to create indexes.
Database Indexing
See how indexes speed up queries by avoiding full table scans
| id | name | age | city | |
|---|---|---|---|---|
| 1 | alice@example.com | Alice Johnson | 28 | New York |
| 2 | bob@example.com | Bob Smith | 35 | Los Angeles |
| 3 | carol@example.com | Carol White | 42 | Chicago |
| 4 | david@example.com | David Brown | 31 | Houston |
| 5 | emma@example.com | Emma Davis | 28 | Phoenix |
| 6 | frank@example.com | Frank Miller | 45 | New York |
| 7 | grace@example.com | Grace Lee | 29 | San Diego |
| 8 | henry@example.com | Henry Wilson | 38 | Dallas |
| 9 | ivy@example.com | Ivy Taylor | 33 | Chicago |
| 10 | jack@example.com | Jack Anderson | 28 | Austin |
Add indexes to columns to speed up queries on those columns.
Composite Indexes(order matters)
A composite index is a single index on multiple columns. The leftmost column is used first for filtering.
SELECT * FROM users WHERE email = 'emma@example.com'How Database Indexes Work
Without Index (Full Table Scan)
The database must check every row in the table to find matches. Slow for large tables!
With Index (Index Seek)
The index acts like a book's index - the database jumps directly to matching rows. Much faster!
When to Create an Index:
- • Columns used frequently in WHERE clauses
- • Columns used in JOIN conditions
- • Columns used for sorting (ORDER BY)
- • High-cardinality columns (many unique values)
🔑 Why Does Composite Index Order Matter?
A composite index like (age, city) is a single B-tree sorted first by age, then by city within each age.
✓ Can use (age, city) index:
- • WHERE age = 28
- • WHERE age = 28 AND city = 'NYC'
- • WHERE city = 'NYC' AND age = 28 (WHERE order does not matter)
✗ Cannot seek with (age, city) index:
- • WHERE city = 'NYC' (the index is sorted by age first)
- • Postgres may still read the whole index, or skip scan in PG 18, but often a seq scan costs less
- • A (city, age) index serves it directly
Understanding Database Indexes
How indexes work
- B-Tree Structure: Most common index type. Like a book's index, sorted for fast lookup.
- Index Seek: With an index, the database jumps directly to matching rows (O(log n)).
- Full Table Scan: Without an index, every row must be checked (O(n)).
Index types
- Single-Column: Index on one column. Great for WHERE clauses on that column.
- Composite: Index on multiple columns. Order matters: (A, B) is not equivalent to (B, A).
- Unique: Enforces uniqueness and provides fast lookups (emails, usernames).
When to index
Good for indexing
- Columns in WHERE clauses
- JOIN columns (foreign keys)
- ORDER BY / GROUP BY columns
- High cardinality (many unique values)
Avoid indexing
- Small tables (<1000 rows)
- Low cardinality (few unique values)
- Frequently updated columns
- Columns rarely used in queries
Index trade-offs
- Storage: Indexes use additional disk space.
- Write Performance: INSERTs/UPDATEs are slower (index must be updated).
- Maintenance: Indexes can become fragmented over time.
- Over-indexing: Too many indexes can hurt more than help.
Overview
An index lets a database find rows without reading the whole table, but only when the query matches the way the index is ordered. This simulator runs queries against a small users table and shows how many rows each one has to examine. Without an index, every query is a full table scan. You can add a B-tree index on a single column or a composite index on two columns, then run queries such as find by email, find by age, an age range, and filters on age and city together. The interesting part is the leftmost prefix rule. A composite index on age and city helps a query that filters on age, and helps more when it filters on both, but it cannot help a query that filters only on city, because city is not the first column. The simulator still lists such an index as present but unusable for seeking. Seeing the scanned row counts change makes it clear why column order matters, and why adding an index for every column is not the answer: each one costs storage and slows down writes.
Try this
- Run "Find by Email" with no index, then add an index on email and run it again to compare the rows examined.
- Create a composite index on age and city and run "Age + City", then run "Find by City" alone and see why it cannot use that index.
- Run "Age Range" with and without an age index to see how a B-tree handles a range.
Frequently asked questions
- What is a database index?
- A separate data structure, usually a B-tree, that keeps the values of one or more columns sorted with pointers to the rows. It lets the database find matching rows without scanning the whole table.
- What is the leftmost prefix rule?
- A composite index on (a, b) is sorted by a first, so it can seek directly for filters on a, or on a and b. A filter on b alone cannot seek into it; the database may still read the whole index or, in some engines, use a skip scan, but usually far less efficiently.
- Why not index every column?
- Every index takes storage and must be updated on each insert, update and delete, so writes get slower. Index the columns your frequent queries filter, join or sort on.
Try next
// simulator
SQL Terminal Simulator
Practice SQL in an interactive browser terminal. Learn SELECT, WHERE, ORDER BY, DISTINCT, aggregates, GROUP BY, HAVING, JOINs, subqueries, and window functions against a small e-commerce schema, with single-table queries evaluated live and verified Postgres output.
// simulator
PostgreSQL Terminal Simulator
Practice the psql tool and Postgres engine in an interactive browser terminal. Explore with meta-commands, read real EXPLAIN plans, add an index to turn a sequential scan into a bitmap index scan, and use BEGIN and ROLLBACK to make changes safe, all against a small e-commerce database with verified PostgreSQL output.
// simulator
MongoDB Terminal Simulator
Practice MongoDB queries in an interactive browser terminal. Learn find and projections, query operators like $lt, $gt, and $in, sort, skip, limit, countDocuments, insert, update, delete, and the aggregation pipeline with $match, $group, and $sort, against a small e-commerce dataset that runs live in your browser.