What is an Index?
Learn what a SQL index is, how B-tree indexes speed up queries, the tradeoffs on writes, and how composite indexes and the leftmost-prefix rule work.
Expected Interview Answer
An index is a separate on-disk data structure, most commonly a B-tree, that stores sorted references to table rows on one or more columns, letting the database locate matching rows in logarithmic time instead of scanning every row in the table.
Without an index, a query filtering on a column forces a full table scan, checking every row one by one. A B-tree index keeps column values sorted in a tree structure with pointers back to the actual rows, so a lookup, range scan, or ORDER BY on that column can jump directly to the relevant section instead of scanning linearly. Indexes speed up reads dramatically but add overhead on writes, since every INSERT, UPDATE, or DELETE must also update each affected index, and they consume additional disk space. A primary key is automatically indexed in virtually every database, and composite indexes on multiple columns follow a leftmost-prefix rule, meaning the index is only useful for queries that filter on its leading column(s) in order.
- Turns full table scans into fast, logarithmic-time lookups
- Speeds up WHERE, JOIN, and ORDER BY on indexed columns
- Can enforce uniqueness when created as a unique index
- Enables efficient range queries on sorted column values
AI Mentor Explanation
An index is like a scorecard sorted by player surname at the back of a stadium program, letting a fan flick straight to 'Sharma' instead of reading every page from the start. Without that sorted index, finding one player's stats means scanning the entire program page by page.
How a B-tree index speeds up a lookup versus a full scan
users table (unsorted rows)
- id
- name
index on email (B-tree, sorted)
- email value
- pointer to row location
Step-by-Step Explanation
Step 1
Identify a slow query
Look for queries doing full table scans on large tables, often found via EXPLAIN.
Step 2
Pick the filter/join/sort column
Choose the column(s) used most often in WHERE, JOIN ON, or ORDER BY clauses.
Step 3
Create the index
Run CREATE INDEX idx_name ON table(column) to build the B-tree structure.
Step 4
Consider composite order
For multi-column indexes, put the most selective or most frequently filtered column first, respecting the leftmost-prefix rule.
Step 5
Verify with EXPLAIN
Re-run the query plan to confirm the optimizer now uses an index scan instead of a full table scan.
Step 6
Monitor write overhead
Watch INSERT/UPDATE/DELETE performance, since every index adds maintenance cost on writes.
What Interviewer Expects
- Explains an index as a sorted structure (typically B-tree) for fast lookups
- Knows indexes speed up reads but slow down writes
- Mentions that primary keys are indexed automatically
- Understands the leftmost-prefix rule for composite indexes
- Can describe when NOT to add an index (low-cardinality or rarely queried columns)
Common Mistakes
- Indexing every column 'just in case', bloating write costs
- Not understanding that a composite index on (a, b) doesn't help queries filtering only on b
- Assuming an index always gets used, without checking selectivity or the query plan
- Forgetting that indexes need to be rebuilt/maintained and consume extra storage
Best Answer (HR Friendly)
“An index is like the index at the back of a textbook — instead of reading every page to find a topic, you jump straight to the right page. Database indexes work the same way, letting the system find matching data quickly instead of scanning the whole table.”
Code Example
-- users: 1,000,000 rows, email is not indexed yet
SELECT * FROM users WHERE email = '[email protected]';
-- Without index: full table scan, checks all 1,000,000 rows
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = '[email protected]';
-- With index: B-tree lookup, checks only a handful of rows
-- Composite index example (leftmost-prefix rule)
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- Helps: WHERE customer_id = 5
-- Helps: WHERE customer_id = 5 AND order_date > '2026-01-01'
-- Does NOT help: WHERE order_date > '2026-01-01' aloneFollow-up Questions
- What is the difference between a clustered and a non-clustered index?
- What is the leftmost-prefix rule for composite indexes?
- Why can too many indexes hurt write performance?
- How does EXPLAIN help you decide whether an index is being used?
- What is a covering index?
MCQ Practice
1. What data structure do most database indexes use internally?
Most general-purpose indexes use a B-tree, which keeps values sorted and supports fast lookups and range scans.
2. What is a downside of adding an index to a table?
Every index must be updated whenever rows are inserted, updated, or deleted, adding write overhead and extra storage.
3. For a composite index on (customer_id, order_date), which query benefits from it?
Composite indexes follow the leftmost-prefix rule, so a query filtering on customer_id (the leading column) can use the index.
Flash Cards
What is a database index? — A sorted structure, typically a B-tree, that stores references to table rows for fast lookups on one or more columns.
What is the main tradeoff of adding an index? — Faster reads, but slower writes and extra storage, since every write must also update the index.
What is the leftmost-prefix rule? — A composite index is only useful for queries that filter on its leading column(s), in order from left to right.
Is a primary key indexed automatically? — Yes — virtually every relational database automatically creates an index on the primary key.