Performance Considerations with Offset and Cursor Pagination in Plain PHP and Doctrine ORM
PHP Performance
Displaying a list of items from the database is probably the most common functionality built for web applications. This is where pagination with LIMIT clause comes into play, which is most likely what you learned to use.
This pattern is called offset pagination. In this post I am going to contrast it with cursor pagination and the performance considerations that come with the choice of pagination algorithm.
Examples are with Plain PHP and Doctrine ORM 3.7, which ships a new CursorPaginator class.
In my test with 300,000 rows, page 4,000 took 127 ms with offset pagination and 3 ms with a cursor.
Offset Pagination Examples
In plain PHP and SQL, you can implement pagination with very little code yourself; you only need to compute the offset, either using that directly in the frontend or from the page and page size.
<?php// threads.php?page=1, threads.php?page=2, ...$pageSize = 20;$page = (int)($_GET['page'] ?? 1);$offset = max(0, ($page-1) * $pageSize);$sql = 'SELECT * FROM thread ORDER BY last_message_at DESC LIMIT ' . $offset . ',' . $pageSize;Doctrine ORM has an API for this as well:
<?phpuse Doctrine\ORM\Tools\Pagination\OffsetPaginator;use Doctrine\ORM\Tools\Pagination\Window; // threads.php?page=1, threads.php?page=2, ...$pageSize = 20;$page = (int)($_GET['page'] ?? 1); $dql = 'SELECT t FROM Thread t ORDER BY t.lastMessageAt DESC, t.id DESC';$query = $entityManager->createQuery($dql);$window = Window::fromPageNumberAndSize($page, $pageSize);$page = (new OffsetPaginator())->paginate($query, $window);Performance Problems with Offset Pagination
But while it’s easy to use, offset pagination has performance downsides: it becomes progressively slower the further you paginate down the list to later pages.
If you use LIMIT 10000,20 to skip the first 10,000 rows, then the database still has to fetch them internally, iterate over, and then drop them.
This becomes more wasteful the higher that offset number is.
Cursor Pagination stays fast on deep pages
Cursor pagination can be used to work around this problem, which avoids using the offset of the LIMIT clause or, more correctly, always uses the offset = 0.
Instead, the offset is simulated by adding a WHERE clause condition on the column(s) used in the ORDER BY of the query.
This keeps the performance of the query similar no matter how deep the user or a robot searches down the list as long as the column(s) are using a database index.
The downside of cursor pagination is that you cannot jump to specific pages other than the first page, next page, and the last page (by switching the order by direction).
Implementing cursor pagination is a bit trickier. Here is the above example in plain PHP:
<?php// $connection is Doctrine DBAL Connection$pageSize = 20;// get the cursor from query or use a default: Lowest value for ASC, Highest for DESC.$cursor = $_GET['next'] ?? null;$where = ["1 = 1"];$params = [];if ($cursor !== null) { $where[] = 'last_message_at <= ?'; $params[] = $cursor;}$sql = 'SELECT * FROM user WHERE ' . implode(' AND ', $where) . ' ORDER BY last_message_at DESC LIMIT 0,' . ($pageSize + 1);$rows = $connection->executeQuery($sql, $params)->fetchAllAssociative(); $nextCursor = null;// it has a next row, so has a next pageif (count($rows) === ($pageSize+1)) { // remove "peeked" row from list, should not be listed on this page. $lastRow = array_pop($rows); // use the same field used for order as the cursor $nextCursor = $lastRow['last_message_at'];}The implementation has the following details:
- The first page has no additional where clause for the cursor.
- Each subsequent page filters by the ordered column.
Doctrine ORM 3.7 will come with a new API to implement cursor-based pagination:
<?php use Doctrine\ORM\Tools\Pagination\CursorPaginator; $cursor = $_GET['cursor'] ?? null; $dql = 'SELECT t FROM Thread t ORDER BY t.lastMessageAt DESC, t.id DESC';$query = $entityManager->createQuery($dql); $paginator = new CursorPaginator(limit: 20);$page = $paginator->paginate($query, $cursor); $threads = $page->getItems();$nextCursor = $page->hasNextPage() ? $page->getNextCursorAsString() : null;Example Performance Data
To demonstrate the negative effects of deep page offset pagination, I ran the example for a demo helpdesk software showing a list of customer support threads/tickets by last message activity using Doctrine ORM offset and cursor pagination.
This is against MySQL 8.4
Between page 1 and page 4000 at 300k rows, you see a 125 ms increase in query time.
For cursor pagination, the query durations stay at 2-3 ms regardless of the page, you can see how on subsequent pages, the query includes the WHERE clause that is not needed on the first page.
Conclusion
Offset pagination is fine as long as nobody pages deep, and it gives you free access to all pages. Once crawlers or API clients walk through long lists, switch to cursor pagination. Doctrine ORM 3.7’s new CursorPaginator makes that switch a small change.
About the author
I am the founder and CEO of Tideways. I started the company over 10 years ago with the mission to move the PHP ecosystem forward, starting with performance. As managing director, I work across product, strategy, and the day-to-day of building a developer-focused SaaS business.
I’m a core contributor to the Doctrine open-source project and a founding board member of the PHP Foundation, which reflects my long-standing commitment to the PHP ecosystem. I particularly enjoy working at the intersection of application performance, developer experience, and the open-source community that makes PHP what it is. Outside of work, I enjoy board games, hiking, and coffee.