⚡ Indexing Visualizer
Database indexes are special lookup tables that the database search engine can use to speed up data retrieval. Learn how B-tree, Hash, and Bitmap indexes work under the hood.
How Indexes Work
An index is a data structure (like a B-tree, Hash table, or Bitmap) that stores a copy of selected columns, organized for fast lookup. Without an index, the database must perform a full table scan — reading every row. With an index, it can locate rows in O(log n) or O(1) time.
B-Tree Index
Balanced tree structure. Best for range queries, sorting, and general-purpose indexing. Default in most databases.
Hash Index
Uses a hash function on the key. Best for exact-match equality lookups. Not suitable for range queries.
Bitmap Index
Uses bit arrays (bitmaps) for each distinct value. Best for low-cardinality columns like gender or status.
Interactive Indexing Simulator
| ID | Name | Salary | Department |
|---|---|---|---|
| 1 | Alice | $45,000 | Engineering |
| 2 | Bob | $55,000 | Marketing |
| 3 | Charlie | $62,000 | Engineering |
| 4 | Diana | $75,000 | Finance |
| 5 | Eve | $48,000 | HR |
| 6 | Frank | $82,000 | Engineering |
| 7 | Grace | $39,000 | Marketing |
| 8 | Henry | $92,000 | Finance |
Full Table Scan
No index — reading all rows sequentially
B-Tree Animation
Watch how a B-tree grows as keys are inserted one by one. The tree stays balanced by splitting overflowing nodes and promoting the middle key.
Press "Insert All Keys" to see the B-tree build
Index Types Comparison
| Type | Best For | Speed | Space |
|---|---|---|---|
| B-Tree | Range queries, sorting, general purpose | O(log n) — search, insert, delete | Moderate — stores keys + pointers |
| Hash | Exact-match equality lookups | O(1) average — constant time | Small — only hash + pointer |
| Bitmap | Low-cardinality columns (status, gender) | Fast bitwise AND/OR operations | Compact — 1 bit per row per value |
SQL Index Examples
Basic Index
Creates a B-tree index on the salary column for faster lookups and range queries.
Unique Index
Ensures all values in the indexed column are unique, while also speeding up searches.
Composite Index
Indexes multiple columns together. The column order matters for query performance (leftmost prefix rule).
Interview Questions
1What is a database index and why is it useful?
A database index is a data structure that improves the speed of data retrieval operations on a table. It works like an index in a book — instead of scanning every page, you jump directly to the relevant location. Indexes drastically reduce query time at the cost of additional storage and slower writes.
2How does a B-tree index work internally?
A B-tree is a self-balancing tree data structure that maintains sorted data and allows O(log n) search, insert, and delete operations. Each node can hold multiple keys (unlike a binary search tree), reducing tree height and disk I/O. Internal nodes guide the search direction, while leaf nodes contain the actual data pointers.
3What is the difference between clustered and non-clustered indexes?
A clustered index determines the physical order of data in the table — there can be only one per table. A non-clustered index stores a separate copy of the indexed columns with pointers to the actual rows. Non-clustered indexes require an extra lookup step but you can create many of them.
4When would you choose a hash index over a B-tree?
Hash indexes are faster for exact-match equality queries (=) since they use O(1) lookup. However, they cannot be used for range queries (<, >, BETWEEN), sorting, or partial matching. B-tree indexes are more versatile and are the default choice for most database workloads.
5What are the downsides of adding too many indexes?
Each index consumes disk space and must be updated on every INSERT, UPDATE, or DELETE operation, slowing down write performance. The query optimizer also spends more time choosing among many indexes. The rule is to index columns used in WHERE, JOIN, and ORDER BY clauses — not every column.