An index is a sorted structure the database can walk instead of reading every row. Grow the table, change the query, and watch rows-examined either stay flat or climb with the table — then look at what the index costs you on every write.
depth 6
Full table scan
4,096
rows examined
Index lookup
1
rows examined
Rows examined, index vs scan4096× fewer
Overview
A B-tree index keeps indexed values ordered with references to table rows. When a query filters on an indexed column, the database can often navigate to matching entries instead of checking every row. An index can also support ordering or a join, depending on its columns and the query.
What an index changes
query.sqlSQLite
CREATE INDEX orders_by_customer
ON orders(customer_id);
Result
This may help queries that select orders for one customer. It does not guarantee the index will be used: a table scan can be cheaper when many rows match or the table is small. The optimizer chooses based on estimates and engine-specific rules.
Match indexes to query patterns
Index columns used in selective filters, join keys, and ordering. A composite index's column order matters; see composite and covering indexes. Expressions or leading wildcards can prevent use of a simple index, although some engines offer specialized indexes for those cases.
Check a query plan before and after creating an index. A SEARCH or index scan is not inherently better than a table scan; measure the workload that matters.
Indexes have a cost
Every extra index uses storage and must be maintained when rows are inserted, updated, or deleted. Too many indexes can slow writes and make cache use worse. Start with the slow query and its plan, add the smallest index likely to help, then measure reads and writes.
Database engines support different structures and options—B-tree, hash, full-text, spatial, or specialized index types—and their guarantees vary. Do not copy an index design from another engine without checking its documentation.
A selectivity example
An index on status may help when only a small fraction of rows have status = 'failed'. If nearly every row is status = 'active', reading the table directly may be cheaper. The same index could still help a query that also filters by a selective customer ID, depending on available composite indexes. The planner uses statistics to estimate this choice; stale statistics can produce poor plans. Maintain statistics using the engine's recommended process, and measure both common and worst-case query shapes before keeping a new index.
Remove indexes that no longer earn their cost
An index that once helped may become redundant after a composite index is added or a query is retired. Inventory index usage using the target engine's statistics, but account for startup time and rare maintenance queries before dropping anything. Compare duplicate indexes, including differences in uniqueness, order, expressions, and included columns. A safe index change is measured on representative traffic and deployed with a rollback path. Avoid changing many indexes at once because it becomes difficult to explain a performance regression.
About to run: set Table Rows to its maximum (20). Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Recall check
0 of 3
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “What an index changes”?
What does this module say about “Match indexes to query patterns”?
What does this module say about “Indexes have a cost”?
Cheat sheet
Indexes and Query Performance
Walk a B-tree instead of reading every row and watch rows-examined collapse. Then meet the queries an index cannot help, and what it costs on writes.
The Ubiquitous B-TreeComer, ACM Computing Surveys 1979
About the author
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.