Indexing & Query Performance
Why some queries are instant and others scan the whole table — and how an index (usually a B-tree) changes that.
Try answering in your head first, then click a question to check the model answer.
Q1.Why doesn't adding an index to every column make a database strictly faster?(show answer)
Every index must be updated on every insert, update, or delete to the indexed column(s), so more indexes mean slower writes and more storage. Indexes are a targeted trade-off — worth it for columns frequently used in WHERE, JOIN, or ORDER BY, not a blanket win applied everywhere.
Q2.Why is a B-tree a good fit for database indexes?(show answer)
A B-tree keeps data sorted and balanced with a low, consistent depth even for huge datasets, giving O(log n) lookups, range scans (since it's ordered, e.g. WHERE age > 30), and efficient inserts/deletes — unlike a hash index, which is great for exact-match lookups but can't efficiently serve range queries or sorted output.
Q3.For a composite index on `(last_name, first_name)`, why does a query filtering only on `first_name` NOT use it efficiently?(show answer)
A composite index is physically sorted first by last_name, then by first_name within each last_name — so it can efficiently jump to a specific last_name prefix, but rows with a given first_name are scattered throughout the index (not contiguous) when last_name isn't also constrained. This is often called the "leftmost prefix" rule.
Q4.You run `EXPLAIN` on a slow query and see a full table scan instead of an index scan. What are common reasons the index isn't being used?(show answer)
Common causes: no index exists on the filtered/joined column; a function is applied to the indexed column in the WHERE clause (e.g. LOWER(email) = ...), which prevents a plain index from being used unless there's a matching functional index; the query optimizer estimates the table is small enough, or the filter isn't selective enough, that a full scan is actually cheaper than using the index.