Efficient Pagination Strategies for DBLE Sharding with MySQL Primary Keys

This technical note, authored by a senior MySQL and DBLE architect, discusses pagination techniques for tables where the DBLE sharding key is also the MySQL physical table primary key, under the constraints of continuous forward or backward paging.

Scenario description : Order or transaction tables generate unique identifiers that serve both as sharding keys and primary keys. Pagination must start from the first page and each subsequent request can only move one page forward or backward.

Direct pagination : Using MySQL's LIMIT [start,] length works well for the first page, but for later pages DBLE cannot push down the limit, causing it to request increasingly larger ranges (e.g., LIMIT 0, n*M) and degrading performance.

Best practice : Keep the limit constant ( LIMIT M or LIMIT 0, M) and filter results by the already‑retrieved IDs. Instead of id NOT IN (retrievedIds...), which becomes inefficient, use an ordered query and apply a simple condition such as id > maxId (or id < minId for backward paging). This lets MySQL discard already‑seen rows without growing the predicate size.

Implementation steps :

Obtain the first page using the regular LIMIT query and record the minimum and maximum id values (minId, maxId).

For forward paging, replace maxId in the query with the previously recorded maxId and execute the statement; update minId and maxId after each fetch.

For backward paging, replace minId similarly and update the bounds accordingly.

Limitations and considerations :

The sharding key must also be the primary key.

Pagination must be performed on a single‑table query; joins break the assumption of a single sharding key per row.

The query must contain an ORDER BY clause ending with the sharding key (e.g., ORDER BY id or ORDER BY ts, id), not the reverse order.

Both first‑page and subsequent‑page queries are broadcast statements, consuming MySQL max_connections; it is recommended to keep concurrent broadcast queries below 10 % of the MySQL max_connections limit.

To protect DBLE memory, ensure that M * dataNodeCount ≤ 8000.

Client‑side pagination logic adds development overhead.

The full SQL examples are provided as images in the original article, and the complete source code can be accessed via the linked Gist: https://gist.github.com/KID-G/4215bb80a16034531f517d27fb865236 .