On a table indexed only on id, why is ORDER BY id LIMIT 10 instant but ORDER BY last_name LIMIT 10 slow?
answer
- one query stops early, the other cannot
- LIMIT is applied after the ordering
- a sort must see every row first
- an index already holds rows in key order
basics
~20 sThe index on id already holds rows in id order, so the engine walks it and stops after ten entries. Nothing indexes last_name, so every row must be read and sorted before the first ten are known.
solid answer
~50 s`LIMIT` is applied **after** `ORDER BY`, so the engine cannot name the first ten rows until it knows the order. There are two ways to get that order. Walking an index whose keys are already in the requested order streams rows out one at a time, so `LIMIT 10` really does let the engine stop after ten — the work is bounded by the LIMIT, not by the table size. If no index provides the order, the engine must add a sort, and a sort has to consume its **entire** input before it can emit its first row. Good engines use a bounded top-N sort that keeps only ten candidates in memory, which saves memory and comparisons, but it still reads every row in the table. The fix is on the authoring side: sort by a column an index can supply, or accept that the sort reads everything.
go deeper
Remember the ordering happens first and the row limit second. Be able to say that an index already stores rows in key order, which is why sorting on an indexed column with a small limit is cheap.
Explain the mechanics: a sort is a blocking operator that must consume its whole input, while an ordered index scan is pipelined and can be stopped early. Mention the bounded top-N sort and be precise that it saves memory, not reads.
Show that you check the plan rather than guess, and that you know the row limit bounds the result but not the work whenever a sort appears. Talk about tie-breakers and result stability for user-facing top-N lists.
Frame it as a scaling property: index-supplied ordering keeps a top-N query flat as data grows, a sorted one degrades linearly. That distinction drives which access patterns you are willing to promise as product features.
## What ORDER BY plus LIMIT actually asks for In SQL's logical evaluation order, `FROM` and `WHERE` produce a row set, `ORDER BY` puts that row set in sequence, and only then does `LIMIT n` (spelled `FETCH FIRST n ROWS ONLY` in the standard) cut it to the first *n*. The row limit is therefore a statement about the *ordered* result, not a licence to look at fewer rows. Whether the engine can actually look at fewer rows depends entirely on where the ordering comes from. ## Two ways to produce order **A sort operator.** The engine collects rows, orders them, and emits them. A sort is *blocking*: it cannot produce its first output row until it has seen its last input row, because until then any unread row might belong at the front. Cost grows with the whole input, and a large sort may spill to temporary storage. **An ordered index scan.** A B-tree index keeps its entries in key order, so reading it front to back yields rows already sorted by that key. This is *pipelined*: row one comes out immediately, row two next, and the consumer can stop whenever it likes. ```sql -- index: people(id); nothing indexes last_name SELECT id, last_name FROM people ORDER BY id FETCH FIRST 10 ROWS ONLY; SELECT id, last_name FROM people ORDER BY last_name FETCH FIRST 10 ROWS ONLY; ``` The first query is an ordered walk that stops after ten entries — the same cost whether the table holds a thousand rows or a billion. The second must feed every row into a sort. ## Why LIMIT does not rescue the sort A common misreading is that `LIMIT 10` tells the sort to "only sort ten rows". It cannot: the ten smallest values are unknown until every value has been examined. Engines do optimise this case with a bounded top-N sort — keep a ten-element structure, compare each incoming row against the worst element, discard immediately if it loses. That turns an N log N sort with a big memory footprint into roughly a linear pass with a tiny one, which is a real and worthwhile saving. What it does **not** do is reduce the number of rows read. The scan underneath still touches the whole table. So the practical rule for a query author: `LIMIT` bounds the work only when the ordering is index-supplied. Otherwise `LIMIT` bounds the *result*, not the *work*. ## What the author controls Several things must line up for the index to supply the order, and most of them are decided in the text of the query rather than in the schema: - **Sort by a bare column, not an expression.** `ORDER BY LOWER(last_name)` cannot use an index on `last_name`, because the index stores `last_name` order. - **Match the index's key sequence.** With keys `(a, b)`, `ORDER BY a` and `ORDER BY a, b` line up; `ORDER BY b` does not. - **Keep the directions consistent.** An index scan yields key order forwards or exactly reversed backwards; a mixed `ASC`/`DESC` list is neither. - **Keep `ORDER BY` and the row limit in the same query block**, and avoid putting an operator between the scan and the limit that throws the order away — a `DISTINCT`, a hash aggregation, or an ordering key computed after grouping. ## Ties and determinism When the ordering key has duplicates, *which* ten rows come back is not determined by the standard: any ten rows that satisfy the ordering are a legal answer, and the same query can return different rows on different runs or after a plan change. If the ten rows must be stable, extend the `ORDER BY` with a column that is unique for the table, such as the primary key. That makes the requested ordering stricter, so check that an index can still supply the extended list. ## Confirming it The question "did I get an index-supplied order?" is answered by the execution plan: a sort step above the scan means you did not. A plan that shows only an ordered index scan feeding the limit means you did, and the query's cost will stay flat as the table grows. ## Portability note The row-limiting clause itself is spelled differently across engines — `LIMIT n` in many, `FETCH FIRST n ROWS ONLY` in the standard, and `OFFSET`/`FETCH` variants elsewhere. The semantics discussed here — applied after ordering, cheap only when the ordering is pipelined — are the same in all of them.
- If the engine keeps only ten candidate rows in memory for the sort, why is the query still slow?Because the memory saving is not a read saving. A bounded top-N sort avoids sorting the whole input and avoids spilling, but the scan beneath it still reads every row in the table and hands each one to the sort. The cost stays proportional to the table size; only the sort's memory and comparison count shrink.
- Does ORDER BY id DESC FETCH FIRST 10 ROWS ONLY still avoid the sort with an ascending index on id?Yes. An index's leaf level is a doubly linked, ordered chain, so the engine can walk it backwards and get exactly the reverse of key order. With a single ordering key, ascending and descending are both free. It stops being free only when a multi-key `ORDER BY` mixes directions, because that is neither key order nor its exact reverse.
- You add LIMIT 10 but no ORDER BY. Which ten rows come back?Any ten. Without `ORDER BY` the result of a query has no defined sequence, so the engine returns whichever ten rows its chosen access path produced first. That can change with a plan change, a new index, concurrent writes, or a version upgrade. Treat an unordered `LIMIT` as a sampling tool, never as pagination or as a top-N.
Finding the first ten names in a phone book is instant because the book is already alphabetical; finding the ten shortest phone numbers means reading every page first, no matter how few you ultimately want.
saying these in an interview costs you the question
- Claims LIMIT makes the engine read only ten rows
- Says LIMIT runs before ORDER BY so only ten rows are sorted
- Thinks any index on the table speeds up any ORDER BY
- Assumes a small result set implies a small amount of work
- Believes ORDER BY DESC always needs a separate descending index