README

Paginating a large table with limit and offset gets slower with every page. To return page 10 000, your database first has to read every row that comes before it, including all the columns you selected.

This package adds a fastPaginate method that does the same job in two steps. It first runs your query with only the primary key selected, so the database can satisfy the whole thing from an index, and then fetches the full rows for the keys on that page.

// Instead of this...
User::query()->where('active', true)->paginate();

// ...do this.
User::query()->where('active', true)->fastPaginate();

The method signature is identical to Laravel's own paginate, and you get back the same paginator, so nothing else in your application has to change.

Support us

We invest a lot of resources into creating best in class open source packages. You can support us by buying one of our paid products.

We highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using. You'll find our address on our contact page. We publish all received postcards on our virtual postcard wall.

Installation

You can install the package via composer:

composer require spatie/laravel-fast-paginate

That's all. The package registers itself, and the fastPaginate method becomes available on every query builder and relation.

The package needs PHP 8.4 and Laravel 13. It is tested against MySQL, PostgreSQL, and SQLite.

Usage

Anywhere you would call paginate, you can call fastPaginate instead.

User::query()->fastPaginate();

User::query()->where('active', true)->orderBy('name')->fastPaginate(perPage: 50);

There's a counterpart for simplePaginate too.

User::query()->simpleFastPaginate();

Relations are supported as well.

$user->posts()->fastPaginate();

$user->roles()->simpleFastPaginate();

If you use Laravel Scout, both methods are available on search builders. Scout already fetches results by key, so these calls are handed straight to Scout's own paginators. They exist so that you don't have to think about which paginator to reach for.

User::search('john')->fastPaginate();

How it works

Rather than asking the database for full rows at an offset, this package runs a query that only touches the primary key.

select id from users order by id limit 15 offset 150000

Because that query only reads one column, the database can often answer it from an index alone, without visiting the table. The keys that come back are then used to fetch the rows you actually asked for.

select * from users where id in (150001, 150002, ...)

This is a variation on a technique called a deferred join. Aaron Francis wrote an in-depth explanation of the theory behind it.

The speedup depends on your data, your indexes, and how deep into the result set your users go. It's unlikely that this approach performs worse than a plain limit and offset, but it is possible, so measure it against your own data.

Queries that can't be optimized

Some queries can't be split into two steps, because a single row of the inner query no longer maps to a single primary key. When fastPaginate sees one of these, it quietly hands the query to Laravel's regular paginator, so it's always safe to call.

That happens when your query has a group by, a having, or a union, when it selects a bound expression that is also used in the order, or when you pass -1 as the page size to fetch every record.

Ordering

Without an explicit order, a database is free to return rows in any order it likes, which would let the two queries disagree about what belongs on the current page. So when your query has no order, this package adds one on the primary key.

If you do order your results, that order is applied to both queries. Any column you select and then order by, such as an alias added by withCount, is kept on the inner query so the sort still works.

Testing

composer test

That runs the suite against an in-memory SQLite database. To run it against MySQL or PostgreSQL instead, point the usual environment variables at a database of your own.

DB_CONNECTION=mysql DB_DATABASE=fast_paginate DB_USERNAME=root composer test

The tests that assert on generated SQL only run on SQLite, since every database writes it a little differently. On the other connections, the suite checks that the deferred join returns exactly what Laravel's own paginator returns.

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

This package is a port of aaronfrancis/fast-paginate by Aaron Francis, who came up with the approach and wrote the original implementation. All credit for the idea, and for most of the code in this package, goes to him.

License

The MIT License (MIT). Please see License File for more information.

spatie / laravel-fast-paginate

Maintainers

Package info

Fund package maintenance!

Statistics

Security