// 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.

Supported by

|Become a sponsor

Database Indexing

See how indexes speed up queries by avoiding full table scans

users table10 rows

id	email	name	age	city
1	[email protected]	Alice Johnson	28	New York
2	[email protected]	Bob Smith	35	Los Angeles
3	[email protected]	Carol White	42	Chicago
4	[email protected]	David Brown	31	Houston
5	[email protected]	Emma Davis	28	Phoenix
6	[email protected]	Frank Miller	45	New York
7	[email protected]	Grace Lee	29	San Diego
8	[email protected]	Henry Wilson	38	Dallas
9	[email protected]	Ivy Taylor	33	Chicago
10	[email protected]	Jack Anderson	28	Austin

Index ManagerIndexes

Add indexes to columns to speed up queries on those columns.

age

city

Composite Indexes(order matters)

A composite index is a single index on multiple columns. The leftmost column is used first for filtering.

(age + city)

(city + age)

Select QueryQuery

SELECT * FROM users WHERE email = '[email protected]'

1-4 select, Enter run, R reset

Queries Run

0ms

Total Time

Index Hits

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.