What is an Index in a Database?
Learn what a database index is, how B-tree indexes speed up queries, the read/write trade-off, which columns to index and common DBMS interview questions.
Expected Interview Answer
A database index is a data structure that speeds up read queries by letting the database find rows without scanning the entire table, at the cost of extra storage and slower writes.
An index (typically a B-tree) keeps a sorted copy of one or more columns plus pointers to the matching rows, so lookups, range queries, and sorts become logarithmic instead of a full linear table scan. The trade-off is that every insert, update, or delete must also maintain the index, and indexes consume disk space — so you index columns used in WHERE, JOIN, and ORDER BY clauses, not every column.
- Turns full-table scans into fast logarithmic lookups
- Accelerates WHERE, JOIN and ORDER BY
- Enables efficient range queries
AI Mentor Explanation
An index is like the alphabetical player list at the back of a cricket almanack. Without it, finding every match a specific player appeared in means reading the whole book cover to cover. With the index, you flip to the name and jump straight to the right pages. The catch: every time a new match is added, the index must be updated too — exactly the write cost a database index pays for its fast reads.
Step-by-Step Explanation
Step 1
Identify slow queries
Find full-table scans on columns used in WHERE, JOIN or ORDER BY.
Step 2
Create the index
Build a B-tree index on the filtering/sorting column(s).
Step 3
Query planner uses it
The optimizer chooses the index to avoid scanning every row.
Step 4
Weigh the write cost
Each insert/update/delete now maintains the index and uses disk — index selectively.
What Interviewer Expects
- Index as a read-speed vs write-cost/storage trade-off
- B-tree as the common index structure
- Which columns to index (WHERE/JOIN/ORDER BY)
- Awareness of composite and unique indexes
Common Mistakes
- Indexing every column "to be safe"
- Believing indexes have no downside
- Ignoring that low-selectivity columns index poorly
- Not knowing indexes slow down writes
Best Answer (HR Friendly)
“A database index is like the index at the back of a book — it lets the database jump straight to the rows it needs instead of scanning the whole table. It speeds up reads dramatically but adds a little cost to writes and storage, so you index the columns you search on.”
Code Example
-- Without an index this scans every row
SELECT * FROM orders WHERE customer_id = 42;
-- Add an index on the filtered column
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Now the same query uses a fast B-tree lookupFollow-up Questions
- What is the difference between a clustered and non-clustered index?
- What is a composite index and does column order matter?
- When can an index hurt performance?
- What is index selectivity?
MCQ Practice
1. What is the main cost of adding an index?
Indexes must be maintained on every write and consume disk space.
2. The most common database index structure is a?
Most relational indexes use a balanced B-tree for logarithmic lookups.
3. Which column is the best index candidate?
Index columns that filter or join queries and have good selectivity.
Flash Cards
What is a database index? — A sorted structure (usually a B-tree) that speeds lookups by avoiding full scans.
Index trade-off? — Faster reads for extra storage and slower writes.
Which columns to index? — Those used in WHERE, JOIN and ORDER BY with good selectivity.
Composite index? — An index on multiple columns; leftmost-prefix column order matters.