cursorPaginate forces order by id asc and messes up the index selection in mysql for incrementing true models

[marius-mcp]

I saw that it is also handling sort on unions.
Question.
I have to bypass 4 functions
(cursorPaginate, paginateUsingCursor, ensureOrderForCursorPagination, enforceOrderBy)
from eloquent builder via macros to change enforceOrderBy

    protected function enforceOrderBy()
    {
        if (empty($this->query->orders) && empty($this->query->unionOrders)) {
            $this->orderBy($this->model->getQualifiedKeyName(), 'asc');
        }
    }

to not force the order by when there are no sorts and the model has incrementing true.

What impact may this have on should Reverse and Unions?

    $orders = $this->ensureOrderForCursorPagination(! is_null($cursor) && $cursor->pointsToPreviousItems());

    protected function ensureOrderForCursorPagination($shouldReverse = false)
    {
        if (empty($this->query->orders) && empty($this->query->unionOrders)) {
            $this->enforceOrderBy();
        }

        if ($shouldReverse) {
            $this->query->orders = collect($this->query->orders)->map(function ($order) {
                $order['direction'] = $order['direction'] === 'asc' ? 'desc' : 'asc';

                return $order;
            })->toArray();
        }

On big data tables of million rows UNIONS sound like a bad bad idea.. just like order by id desc as it will mess up the index selection in mysql.

[marius-mcp]

I see that the asc from query determines the id > or < in where

Also I see there orWhere which is again messing up the indexes...

[marius-mcp]

It seems I need to implement it by overwriting another function paginateUsingCursor to bypass these orWhere and handle no sort in the condition..... great...

[marius-mcp]

PLEASE someone fix this in the core:

Main changes needed are:

\Illuminate\Database\Eloquent\Builder::ensureOrderForCursorPagination

    /**
     * Ensure the proper order by required for cursor pagination.
     *
     * @param  bool  $shouldReverse
     * @return \Illuminate\Support\Collection
     */
    protected function ensureOrderForCursorPagination($shouldReverse = false)
    {
-        if (empty($this->query->orders) && empty($this->query->unionOrders)) {
-            $this->enforceOrderBy();
-        }

+        if ([] === ($this->query->orders ?? []) && [] === ($this->query->unionOrders ?? [])) {
+           //  if (!$this->model->incrementing || $shouldReverse) { // first solution NOK
+            if (!$this->model->incrementing || $shouldReverse || $this->query->wheres !== []) {
+                $this->orderBy($this->model->getQualifiedKeyName());
+            }
+        }

        if ($shouldReverse) {
            $this->query->orders = collect($this->query->orders)->map(function ($order) {
                $order['direction'] = $order['direction'] === 'asc' ? 'desc' : 'asc';

                return $order;
            })->toArray();
        }

        if ($this->query->unionOrders) {
            return collect($this->query->unionOrders);
        }

        return collect($this->query->orders);
    }

and
\Illuminate\Database\Concerns\BuildsQueries::paginateUsingCursor

    /**
     * Paginate the given query using a cursor paginator.
     *
     * @param  int  $perPage
     * @param  array|string  $columns
     * @param  string  $cursorName
     * @param  \Illuminate\Pagination\Cursor|string|null  $cursor
     * @return \Illuminate\Contracts\Pagination\CursorPaginator
     */
    protected function paginateUsingCursor($perPage, $columns = ['*'], $cursorName = 'cursor', $cursor = null)
    {
        if (! $cursor instanceof Cursor) {
            $cursor = is_string($cursor)
                ? Cursor::fromEncoded($cursor)
                : CursorPaginator::resolveCurrentCursor($cursorName, $cursor);
        }

        $orders = $this->ensureOrderForCursorPagination(! is_null($cursor) && $cursor->pointsToPreviousItems());

        if (! is_null($cursor)) {
            $addCursorConditions = function (self $builder, $previousColumn, $i) use (&$addCursorConditions, $cursor, $orders) {
                $unionBuilders = isset($builder->unions) ? collect($builder->unions)->pluck('query') : collect();

                if (! is_null($previousColumn)) {
                    $originalColumn = $this->getOriginalColumnNameForCursorPagination($this, $previousColumn);

                    $builder->where(
                        Str::contains($originalColumn, ['(', ')']) ? new Expression($originalColumn) : $originalColumn,
                        '=',
                        $cursor->parameter($previousColumn)
                    );

                    $unionBuilders->each(function ($unionBuilder) use ($previousColumn, $cursor) {
                        $unionBuilder->where(
                            $this->getOriginalColumnNameForCursorPagination($this, $previousColumn),
                            '=',
                            $cursor->parameter($previousColumn)
                        );

                        $this->addBinding($unionBuilder->getRawBindings()['where'], 'union');
                    });
                }

                $builder->where(function (self $builder) use ($addCursorConditions, $cursor, $orders, $i, $unionBuilders) {
-                    ['column' => $column, 'direction' => $direction] = $orders[$i];
+                    ['column' => $column, 'direction' => $direction] = $orders[$i] ?? [
+                       'column' => $this->model->getQualifiedKeyName(),
+                       'direction' => 'asc'
+                    ];

                    $originalColumn = $this->getOriginalColumnNameForCursorPagination($this, $column);

                    $builder->where(
                        Str::contains($originalColumn, ['(', ')']) ? new Expression($originalColumn) : $originalColumn,
                        $direction === 'asc' ? '>' : '<',
                        $cursor->parameter($column)
                    );

                    if ($i < $orders->count() - 1) {
                        $builder->orWhere(function (self $builder) use ($addCursorConditions, $column, $i) {
                            $addCursorConditions($builder, $column, $i + 1);
                        });
                    }

                    $unionBuilders->each(function ($unionBuilder) use ($column, $direction, $cursor, $i, $orders, $addCursorConditions) {
                        $unionBuilder->where(function ($unionBuilder) use ($column, $direction, $cursor, $i, $orders, $addCursorConditions) {
                            $unionBuilder->where(
                                $this->getOriginalColumnNameForCursorPagination($this, $column),
                                $direction === 'asc' ? '>' : '<',
                                $cursor->parameter($column)
                            );

                            if ($i < $orders->count() - 1) {
                                $unionBuilder->orWhere(function (self $builder) use ($addCursorConditions, $column, $i) {
                                    $addCursorConditions($builder, $column, $i + 1);
                                });
                            }

                            $this->addBinding($unionBuilder->getRawBindings()['where'], 'union');
                        });
                    });
                });
            };

            $addCursorConditions($this, null, 0);
        }

        $this->limit($perPage + 1);

        return $this->cursorPaginator($this->get($columns), $perPage, $cursor, [
            'path' => Paginator::resolveCurrentPath(),
            'cursorName' => $cursorName,
-            'parameters' => $orders->pluck('column')->toArray(),
+           'parameters' => ($p = $orders->pluck('column')->toArray()) !== [] ?
+                $p :
+                [$this->model->getQualifiedKeyName()],
        ]);
    }

[marius-mcp]

Created an issue that got ignored FYI: #51691

I tweaked my brain in solving this so the issue being rejected due to improper formatting is above my patience at this moment:)

[marius-mcp]

My bad. I apologize. It seems that if an index is used, the results will be sorted by that index asc so the sort is needed....

So my above suggestion of fix is valid only when there are no filters applied.

so

if (!$this->model->incrementing || $shouldReverse || $this->query->wheres !== []) {

instead of

if (!$this->model->incrementing || $shouldReverse) {

Updated the comment #51644 (comment)

So the fix will just transform a query from:

select * from table order by table.id asc limit 3;

to

select * from `table` limit 3"

AND

select * from `table` where (`table`.`id` > 2) order by table.id asc limit 3"

to

select * from `table` where (`table`.`id` > 2) limit 3"

When conditions are present, there will always be a sort applied.

select * from `table` where `operations`.`parent_id` is not null order by `table`.`id` asc limit 3"

Benchmark on table with '3134570' rows in Mysql 8:

With fix:
0,247 sec, sql: select SQL_NO_CACHE * from table limit 1001
0,246 sec, sql: select SQL_NO_CACHE * from table where (table.id > 2168) limit 1001

With fix and condition:
0,198 sec, sql: select SQL_NO_CACHE * from table where table.id <= '6000' order by table.id asc limit 1001
0,106 sec, sql: select SQL_NO_CACHE * from table where table.id <= '6000' and (table.id > 2168) order by table.id asc limit 1001

Without fix:
0,287 sec, sql: select SQL_NO_CACHE * from table order by table.id asc limit 1001
0,247 sec, sql: select SQL_NO_CACHE * from table where (table.id > 2168) order by table.id asc limit 1001