Which pagination strategy fits a 40M-row Phalcon table: QueryBuilder paginator COUNT cost or manual keyset?

Short answer

At 40 million rows with infinite scroll on the roadmap, manual keyset pagination is the defensible choice. The QueryBuilder paginator's COUNT plus deep OFFSET scans are both costs that grow with the table, and keyset eliminates them while matching the UI you already plan to build. Caching the COUNT is a workable bridge only while the page-number grid still exists.

Why the paginator's costs dominate at this scale

Phalcon\Paginator\Adapter\QueryBuilder does two things that hurt on a large InnoDB table:

• It issues a separate COUNT(*) to compute total_pages. Without a covering index this scans a large fraction of the table, and the number is stale the moment a concurrent write lands — so you pay the cost and still get an approximate answer.
• It applies LIMIT n OFFSET m. InnoDB must read and discard m rows before returning the page, so latency grows roughly linearly with page depth. Page 10 is fine; page 5,000 is not.

Verify this on your own data before committing: run EXPLAIN on the generated COUNT query, and time the page query at offsets of 100, 10,000, and 1,000,000 rows. If the deep-offset times are already in the hundreds of milliseconds at 40M rows, the trend only gets worse.

Can the paginator skip the COUNT?

There is no documented configuration flag on the QueryBuilder adapter to skip or approximate the COUNT — computing totals is part of what the adapter does. If you want paginator-shaped results without the COUNT, you write a custom adapter (implement the paginator interface, run only the page query, and populate the result fields yourself). That is a reasonable pattern, but at that point you are maintaining your own pagination logic anyway, which weakens the argument for keeping the adapter versus doing keyset directly.

Keyset pagination and the result contract

Keyset pagination — WHERE id > :cursor ORDER BY id LIMIT n — turns page depth into a constant-time indexed lookup. The trade-offs are real but manageable:

• total_items / last: you no longer know them. For infinite scroll this is irrelevant; consumers need only "is there a next page," which you get by fetching n + 1 rows and checking whether the extra row exists.
• next: instead of a page number, you return the last row's id as the cursor. Any consumer expecting numeric next/previous page fields must be updated to pass an opaque cursor.
• Stable sort: the cursor column must be unique and indexed — the auto-increment primary key is ideal. If you later sort by a non-unique column (e.g., created_at), append the id as a tiebreaker: WHERE (created_at, id) > (:ts, :id) ORDER BY created_at, id, or pages will skip/duplicate rows.

So yes, keyset conflicts with the standard paginator result contract — but only the fields your future UI doesn't need. If you keep the page-number grid for now, a hybrid works: use the QueryBuilder paginator with the COUNT cached (e.g., in Redis with a short TTL or write-triggered invalidation), and expose keyset endpoints for the infinite-scroll path. Accept that the cached total is approximate; display it as such.

Recommendation

1. Build the read path around keyset pagination on the primary key now; it serves both current and planned UIs and never needs to be thrown away.
2. If the grid must show total pages temporarily, cache the COUNT and treat it as an estimate — do not let it block the migration to cursor navigation.
3. Benchmark both approaches against a representative dataset (or a production read replica) before finalizing: EXPLAIN the COUNT, time deep offsets, and compare against the keyset query at the same page sizes.

One caveat worth flagging: if your grid lets users jump to an arbitrary page number ("go to page 12,000"), keyset cannot serve that directly — that feature and 40M-row performance are fundamentally in tension, and it is usually the feature that has to go.