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'
✗ Cannot use (age, city) index:
- • WHERE city = 'NYC' (needs city first)
- • Need (city, age) index instead
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.
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.