Improve pagination performance without using `OFFSET`

[yaquawa]

Currently the pagination logic generates OFFSET LIMIT ? in the SQL. (see https://github.com/laravel/framework/blob/5.7/src/Illuminate/Database/Eloquent/Builder.php#L746)

which brings a huge performance impact when querying large database. (See more detail at Faster Pagination in Mysql – Why Order By With Limit and Offset is Slow?)

Please consider to support a cursor based pagination. We can introduce a new method for cursor based pagination. The signature of the cursor based pagination would be

public function paginateAfter
(
$cursor, // the value of cursor. for example `212`
$cursorColumn, // the column name of cursor. for example `id`
$perPage = null,
$columns = ['*'],
$pageName = 'page',
$page = null
)

What do you think? If this is OK to have I'll send a PR for this.

[mfn]

$cursorColumn

Note that it's not uncommon to have more than one column for that.

Example:

• you might want to primarily sort by date but only have second-resolution
• you have multiple entries for the date
• to guarantee a stable result (especially when loading the next page) you may want to additional sort by id and exclude it on the next page
• this also means your $cursor can be a combination of more than one value

You might be interested in https://github.com/laravel/framework/pull/22446 which added a very low-level statement support which can be used for building this.

[yaquawa]

@mfn Thanks for your comment! I didn't even notice Laravel could do this https://github.com/laravel/framework/pull/22446 ! Nice job and good to know!!

As you described, endless scrolling/loading doesn't need a total count, if you work with the endless scrolling/loading, the current pagination logic can generates an extra query as well.

And this "endless scrolling/loading" is getting more and more popular nowadays. I think it's worth to get this feature into the framework now.

Do you have any better suggestion on this idea? Thanks.

[spawnia]

This is attempted in https://github.com/laravel/framework/pull/37216

[mpyw]

According to SQL Feature Comparison, SQLServer does not support Tuple Comparison syntax. So

(a, b, c) > (1, 2, 3)

should be rewritten to

a=1 and b=2 and c>3
or
a=1 and b>2
or
a>1

If you use SQLServer, still lampager/lampager-laravel: Rapid pagination for Laravel may help implementing cursor pagination.