ORDER BY With LIMIT Is Fast Only When an Index Supplies the Order
LIMIT 20 does not mean the database only handles 20 rows. If no index provides the requested order, it must produce every qualifying row, sort them, and throw nearly all of them away — the plan shows a Sort under the Limit. With an index in the right order and direction, the Limit stops the scan after 20 rows and the work is constant no matter how big the table gets.
Questions this Concept answers
- Why is `LIMIT 20` not enough on its own to make a query cheap?
11.4. Indexes and ORDER BY
An index can do more than find rows; a B-tree can also hand them back already in order, which lets a query skip a separate sorting step. That matters most with ORDER BY combined with LIMIT: without a…