Pagination ... 63 seconds to return

I have a couple large tables 900k accounts and 1 million plus orders ....... I am trying to figure out a way to paginate this data to make it useable for customer service.

My Accounts table takes over 1 minute to render the page of paginated data. The orders times out. I am using the fastPaginate() package that helped. I am currently doing this

$accounts = Account::on('mysql_unbuffered')->select('id', 'firstname', 'lastname', 'active')->where(function ($query) use ($request) {
            if ($term = $request->q) {
                $query->where('lastname', 'LIKE', '%' . $term . '%');
            }
            if (isset($request->search) && count($request->search) > 0) {
                foreach ($request->search as $s) {
                    $query->where($s, 'LIKE', '%' . $request->q . '%');
                }
            }
            return $query->get();
        })->autosort()->fastPaginate(config('pagination.pageListSize'));

The initial query for all users is taking 47 seconds alone.

select * from `accounts`

I am using an unbuffered mysql connection ...... I need to figure this out as we are moving from an old CakePHP 2.x app to laravel and pagination load time is the constant complaints from my biz partners (not programmers)

Thx for any input.

LaryAI

2 years ago

Best Answer

Level 58

The slow performance of the pagination can be improved by optimizing the query and using proper indexing. Here are a few suggestions to improve the performance:

Indexing: Make sure that the columns used in the WHERE clause (lastname, $request->search) are indexed in the database. This will significantly speed up the search queries.
Select only required columns: Instead of selecting all columns (*), specify only the columns that are needed for the pagination. This will reduce the amount of data fetched from the database and improve performance.
Use eager loading: If the Account model has any relationships with other models (e.g., orders), consider using eager loading to fetch the related data in a more efficient way. This can help reduce the number of queries executed.
Use paginate() instead of fastPaginate(): The fastPaginate() method may not always provide the best performance. Try using the built-in paginate() method provided by Laravel and see if it improves the performance.

Here's an updated version of the code with the suggested improvements:

$accounts = Account::on('mysql_unbuffered')
    ->select('id', 'firstname', 'lastname', 'active')
    ->when($request->q, function ($query, $term) {
        $query->where('lastname', 'LIKE', '%' . $term . '%');
    })
    ->when($request->search && count($request->search) > 0, function ($query) use ($request) {
        foreach ($request->search as $s) {
            $query->orWhere($s, 'LIKE', '%' . $request->q . '%');
        }
    })
    ->with('orders') // Replace 'orders' with the actual relationship name
    ->paginate(config('pagination.pageListSize'));

// Access the orders for each account
foreach ($accounts as $account) {
    $orders = $account->orders;
    // Do something with the orders
}

Make sure to replace 'orders' in the with('orders') method with the actual relationship name between the Account and Order models.

Remember to also check the database indexes and optimize them accordingly.

1 like

ehask71

2 years ago

Level 1

@LaryAI 100% better thank you ..... it was driving me nuts. How CakePHP 2.x EOL forever and a day and its pagination was not batting an eyelash. I assumed I was doing something wrong and I was!

krisi_gjika

2 years ago

Level 14

the return $query->get(); tells me you were not paginating anything.

ehask71

2 years ago

Level 1

@krisi_gjika pagination was working with the above code it was taking 63 secs to load .... but it was displaying

Snapey

2 years ago

Level 122

@krisi_gjika The return is inside the closure.

But no need to return or do the get() so these should be removed @ehask71

1 like

Please or to participate in this conversation.