Does SQL LIMIT Read All Rows? ORDER BY, Indexes, and Keyset Pagination

The short answer is: LIMIT limits the rows returned, not the rows inspected. A query can return 10 rows and still read a very large part of a table if the database has to discover which 10 rows come first.

This is a common interview trap because the SQL looks small:

SELECT id, account_id, created_at, event_name
FROM events
WHERE account_id = 42
ORDER BY created_at DESC
LIMIT 10;

The result has 10 rows. The work behind it depends on whether the database can find rows for account 42 already ordered by created_at. If not, it may scan candidates, sort them, and only then apply the limit.

For adjacent fundamentals, see why SQL indexes are not used and how to read SQL EXPLAIN plans.

The Mental Model

Think about a top-N query in three steps:

• Filter: Which rows are candidates?
• Order: Which candidate rows come first?
• Limit: How many ordered rows should be returned?

If the database cannot produce rows in the requested order directly, it has to evaluate enough candidates to know which rows belong at the top. LIMIT 10 does not magically tell the database where the best 10 rows live.

A good interview answer is: LIMIT can let the database stop early only when the execution plan can produce rows in the requested order. That usually requires a matching index or a very selective filter.

A Concrete Table

Use this simplified event table:

CREATE TABLE events (
    id BIGINT PRIMARY KEY,
    account_id BIGINT NOT NULL,
    event_name TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    payload JSONB
);

Suppose account 42 has hundreds of thousands of events. This query asks for the latest 10:

SELECT id, event_name, created_at
FROM events
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 10;

The extra id DESC is important. It makes the order deterministic when two rows have the same timestamp. Without a tie-breaker, repeated runs can return the same logical top 10 in a different order, and pagination can skip or duplicate rows.

What Happens Without the Right Index

Without a useful index, a database may do work like this:

• Scan many rows in events.
• Keep rows where account_id = 42.
• Sort the matching rows by created_at DESC, id DESC.
• Return the first 10 rows.

Some engines optimize the sort with a top-N heap. That can avoid fully sorting every candidate row, but it does not avoid reading the candidates. The scan can still dominate the runtime.

In EXPLAIN ANALYZE, watch for signals like:

• A sequential scan or large bitmap scan.
• An explicit sort step.
• A large gap between rows inspected and rows returned.
• High actual time before the limit node returns rows.

The slow part is not that the result has 10 rows. The slow part is finding which 10 rows are correct.

The Index That Lets LIMIT Stop Early

For this query, the useful index matches the equality filter first, then the order:

CREATE INDEX events_account_created_id_idx
ON events (account_id, created_at DESC, id DESC);

Now the database can go directly to account 42 in the index, walk rows in newest-first order, read 10 entries, and stop.

The query did not change:

SELECT id, event_name, created_at
FROM events
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 10;

The physical access path changed. The database no longer has to discover the order by sorting a large candidate set. The index already stores the rows in the order the query needs.

Column Order Matters

The index should reflect the query shape. This index is useful:

ON events (account_id, created_at DESC, id DESC)

This one is usually less useful for the same query:

ON events (created_at DESC, account_id, id DESC)

The second index is ordered globally by time. It might help if you ask for the latest events across all accounts. But for one account, the database may still need to scan through many rows for other accounts while looking for account 42.

For a query with account and status filters:

SELECT id, created_at
FROM events
WHERE account_id = 42
  AND status = 'failed'
ORDER BY created_at DESC, id DESC
LIMIT 10;

A stronger candidate is:

CREATE INDEX events_account_status_created_id_idx
ON events (account_id, status, created_at DESC, id DESC);

Equality filters generally come before ordered columns. Range filters and optional filters require more judgment, so verify with the actual plan instead of guessing.

Covering Indexes Can Help, But Do Not Overdo It

If the query only needs id, created_at, and a small status column, an index may contain everything needed. If the query selects a large payload column, the database may still visit the table for those 10 rows.

That is usually fine. Reading 10 table rows after an ordered index walk is very different from scanning a million rows. The mistake is creating a huge index that includes large text or JSON columns just to avoid a small number of table lookups.

OFFSET Pagination Still Gets Slower

An index helps this query stay ordered:

SELECT id, created_at
FROM events
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
OFFSET 100000
LIMIT 20;

But deep offset pagination still makes the database walk past the first 100,000 ordered rows before returning 20. The result page is small, but the skipped rows are still work.

For feeds, logs, exports, and infinite scroll, keyset pagination is usually better:

SELECT id, created_at
FROM events
WHERE account_id = 42
  AND (created_at, id) < (timestamp '2026-05-01 12:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The previous page gives you the last created_at and id. The next query resumes from that point instead of counting and skipping rows.

Interview Answer Template

If asked whether ORDER BY created_at LIMIT 10 reads only 10 rows, use this answer:

• No, not necessarily. LIMIT limits the result size.
• The database can stop early only if the plan produces rows in the requested order.
• Without a matching index, it may scan many candidates and perform a top-N sort.
• For WHERE account_id = ? ORDER BY created_at DESC, id DESC LIMIT 10, an index on (account_id, created_at DESC, id DESC) is the natural starting point.
• For deep pagination, avoid large OFFSET values and use keyset pagination when the product allows it.

Practice Exercise

Use this table:

orders(
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC NOT NULL
)

Question 1: Write a query for the latest 25 completed orders for one customer.

SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 10
  AND status = 'completed'
ORDER BY created_at DESC, id DESC
LIMIT 25;

Question 2: Propose an index.

CREATE INDEX orders_customer_status_created_id_idx
ON orders (customer_id, status, created_at DESC, id DESC);

Question 3: Explain why id DESC is included.

It makes the order deterministic when multiple orders have the same timestamp. That prevents unstable results and gives keyset pagination a reliable cursor.

Production Checklist

• Always pair LIMIT with a deterministic ORDER BY when order matters.
• Include a unique tie-breaker in the order, usually the primary key.
• Index equality filters before ordered columns for common top-N queries.
• Check actual rows read, not just rows returned.
• Use keyset pagination for deep pages when users browse sequentially.
• Avoid speculative indexes. Each index slows writes and uses storage.
• Recheck plans after data distribution changes.

FAQ

Does LIMIT make a SQL query faster?

Sometimes. It reduces the final result size and can allow early stop when the plan produces rows in the requested order. It does not automatically prevent scans, joins, filtering, or sorting.

Should every ORDER BY get an index?

No. Index frequent, high-value query patterns. Sorting a few hundred filtered rows is usually fine. Maintaining many low-value indexes can hurt writes and storage.

Is OFFSET always bad?

No. Small offsets are fine. Large offsets on large ordered result sets become expensive because the database still has to walk past skipped rows. Use keyset pagination when deep sequential browsing matters.

Why does the index direction matter?

Modern databases can often scan indexes in either direction, but matching the query order clearly helps the planner and keeps composite index design easier to reason about. Always verify with EXPLAIN ANALYZE.