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.
Find by Email
Goal: Run a query with no index and watch the full table scan. Pick any query and press Run query.
Indexes
Query
SELECT * FROM users WHERE email = 'emma@example.com'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.