Why queries are slow and how indexes actually work.
By Mehedi Hasan Sarkar · Updated Jul 12, 2026 · advanced
Intuition
Imagine looking for a recipe in a huge cookbook. If you have to read every page, that takes a long time. A table of contents or index lets you jump straight to the right page range instead of scanning the whole book.
Database indexes work the same way. They trade extra space and maintenance work for much faster lookups, especially when you only need a small slice of rows out of a very large table.
A query is slow when the database must inspect too many rows, sort too much data, or move large intermediate results around. An index is a separate structure the database can use to narrow the search quickly instead of doing a full table scan.
This matters most when filters are selective, joins are large, or sorting/grouping would otherwise touch many rows. But an index is not free: every insert, update, and delete may also need to update the index, so the optimizer only uses it when the speedup outweighs the cost.
Most general-purpose indexes are B-trees. They keep keys in sorted order and branch down through a small number of pages, so a lookup takes roughly O(log n) page visits instead of scanning every row.
Think of it like navigating a filing cabinet by tabs: each level tells you which smaller range to open next. Databases like B-trees because they are good for exact matches, ranges, and ordered scans.
A key detail is that the database usually stores index entries in pages, not one row at a time. That page-oriented design is why locality matters: nearby keys are often nearby on disk or in memory.
A B-tree can satisfy predicates like WHERE created_at >= ... by walking to the first matching key and then scanning forward. That is much cheaper than checking every row.
It also helps ORDER BY when the requested order matches the index order. In that case, the database can read rows already sorted, avoiding a separate sort step.
This is why the same index can speed up both WHERE and ORDER BY, but only when the query shape matches the key order.
A composite index stores multiple columns in one sorted key, such as (country, status, created_at). The order matters because the database can only efficiently use the leftmost prefix of the key for navigation.
That means an index on (a, b, c) can usually help queries filtering on a, or a plus b, or all three. It is much less useful for a query filtering only on b.
Interview trap: people often think “more columns in the index is always better.” In reality, the best order depends on selectivity, equality vs range filters, and whether you need sorting on later columns.
A covering index contains all the columns a query needs, so the database can answer it using only the index pages. That avoids a second hop back to the table for each matching row.
This is especially valuable when a query returns many matching rows but only a few columns. If the index covers the SELECT list and the filter, the engine can do an index-only plan and reduce random I/O.
For example, if you query SELECT status, created_at FROM orders WHERE customer_id = ?, an index on (customer_id, status, created_at) may fully cover it. Without coverage, the database might still use the index to find rows but then visit the table for each match.
An EXPLAIN plan shows how the optimizer intends to execute the query: scan type, join order, index choice, row estimates, and whether sorting or hashing is required.
Look for signs of trouble:
Seq Scan or full table scan when you expected an index.Sort, Hash, or Nested Loop work on big inputs.Using where; Using filesort or similar markers depending on the database.The key skill is not memorizing output text, but spotting whether the engine is filtering early, using the right index, and avoiding unnecessary work.
Suppose you run this query often:
SELECT id, created_at FROM orders WHERE customer_id = ? AND status = 'paid' ORDER BY created_at DESC LIMIT 20;A strong candidate is a composite index like (customer_id, status, created_at DESC).
Why it helps:
customer_id and status narrow the search quickly.created_at DESC matches the requested ordering.id may still require a table lookup unless included by your database’s covering mechanism.If the table has millions of rows, the database can jump into the relevant key range, read only the newest paid orders for that customer, and stop after 20 rows.
EXPLAIN SELECT id, created_at
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;If you query WHERE LOWER(email) = 'a@x.com' but the index is on email, the database may not be able to use that index efficiently because the predicate changes the column value.
A function on the indexed column often breaks direct index navigation unless you use a matching functional index. The engine may fall back to scanning many rows, even though an index exists.
This is a common interview gotcha: having an index is not the same as having a usable index. The query shape must match the index shape.