Minimizing pager queries for large datasetsfor Drupal 11 , 10 , 9 , and 8

Flip to page 4,000 of a 100,000-row table sorted by date, and something surprising happens: the database has to scan and discard the first 39,990 rows just to hand you the 10 you actually asked for. Pagination looks free from the front end — a couple of "Next" clicks — but the further into a large dataset you page, the more expensive each page genuinely becomes. This lesson is about that hidden cost, and the two ways Views lets you avoid it.

What you'll learn in this lesson

• Why traditional OFFSET-based pagination gets slower the deeper a user pages
• How Views' "Mini" pager avoids one expensive query the default "Full" pager always runs
• What cursor-based (keyset) pagination is, and when it's worth the extra setup
• A practical rule of thumb for choosing a pager based on your actual dataset size

Why OFFSET gets slower the deeper you page

SELECT * FROM node_field_data
ORDER BY created DESC
LIMIT 10 OFFSET 39990

This is roughly what "page 4,000" of a 10-per-page View compiles down to. Most database engines can't jump straight to row 39,991 — they have to walk through and discard everything before it first. OFFSET 0 and OFFSET 10 are essentially free; OFFSET 39990 means real, measurable work on every request, and it only gets worse as the table grows or the page number climbs.

The Full pager's hidden second query

Views' default "Full" pager (the one with numbered page links: 1, 2, 3 ... last) needs to know the *total* row count to render those page links and figure out where "last" is — so it runs a second, separate COUNT(*) query on top of the actual data query, every single time the View loads. On a huge, frequently-changing table, that count query can be nearly as expensive as the real query it's paired with.

Cursor-based pagination for truly large datasets

For a dataset large enough that even Mini's OFFSET-based paging feels slow deep in, the real fix is cursor (keyset) pagination: instead of "skip N rows," you filter with "give me rows after the last one I saw" — WHERE created < :last_seen_created ORDER BY created DESC LIMIT 10. This turns an expensive OFFSET scan into a cheap indexed range lookup, at the cost of losing the ability to jump to an arbitrary page number (you can only go forward/backward from where you are). Views doesn't ship this out of the box, but the views_infinite_scroll and similar contrib pager plugins implement exactly this pattern if your dataset genuinely needs it.

Quick check: a View shows the 25 most recent items and almost nobody ever clicks past page 2. Is switching to the Mini pager or investing in cursor pagination the better use of your time? (Mini pager — the dataset visitors actually page through is small in practice, so the OFFSET cost is negligible; the easy win is dropping the Full pager's redundant COUNT(*) query. Cursor pagination earns its complexity only when people are genuinely paging deep into a large table.)

Key takeaways

• OFFSET-based pagination gets slower the deeper a user pages — the database still has to scan past every skipped row.
• Views' default "Full" pager runs an extra COUNT(*) query just to build its numbered page list; the "Mini" pager skips it entirely.
• Switch to Mini whenever your UI doesn't actually need numbered page links — it's a free win with zero code.
• Reach for cursor/keyset pagination (via a contrib pager plugin) only once a dataset is large enough that even Mini's OFFSET cost becomes noticeable.

Coming up next

We've optimized what the query fetches and how it's paged — next we look at the other end of the pipeline: how the View's own output gets rendered, and how a heavier-than-necessary template can undo all of that database-side work.