How to speed up query with groupBy?

Question

SQL

How can you speed up sampling? There are a lot of records

$rows->get()->groupBy(function ($item) {
                    return sprintf(
                        '%s by %s',
                        $item->created_at->format('Y-m-d H:i:s'),
                        is_null($item->user) ? 'undefined' : $item->user->getNameAttribute()
                    );
                }
            )->paginate($paginate);

The result of my selection

Answer 1

If this is constantly needed, then you can denormalize: add a table model with a grouping value (return sprintf(
'%s by %s',
$item->created_at->format('Ymd H:i:s'),
is_null ($item->user) ? 'undefined' : $item->user->getNameAttribute()
); and that's all), and the field in the source table with its id, make an index by the grouping value, get only the necessary grouping values from this table, and then, after receiving the page, already get the necessary data of the source table there, for example, through https://laravel.com/docs/8.x/eloquent-relationship...

Answer 2

You are confusing a query to the database and working with a collection.
$rows->get() - has already returned a bunch of garbage to you, and then you work with it on the php side. Do grouping on the DB side using sql
$rows->groupBy(...)->get()