What Every Developer Should Know About SQL Indexes in Interviews

Concept in Simple Words: A database index is like the index at the back of a textbook. Instead of reading the entire book page-by-page (a full table scan), you look up a keyword and jump straight to the relevant page (index scan). Indexes speed up read queries, but slow down write operations because the index must be updated on every INSERT, UPDATE, or DELETE.

Deep Walkthrough & Code: Let's look at a query filtering on user accounts and how a composite index functions:

CREATE INDEX idx_user_status_date ON users (status, created_at);
SELECT * FROM users WHERE status = 'active' AND created_at > '2026-01-01';

Step-by-Step Dry Run: Suppose we run this query on a table with 1,000,000 users. The database index is a balanced tree (B-Tree). Instead of scanning 1,000,000 rows, it searches the B-Tree for status='active', then scans the matches sorted by created_at. This reduces disk I/O operations from 1,000,000 to just a few dozen page reads.

Production Level Issue & Fix: The Left-Prefix Rule. An index on `(status, created_at)` cannot be used efficiently if the query filters ONLY on `created_at` (e.g. `WHERE created_at > '2026-01-01'`). In production, composite index columns must be ordered based on query filtering requirements, starting with the most frequently filtered, equality-constrained columns.