CODEWITHSUNDEEP
phpmysqldatabaselaraveleloquent
Deemantha
Deemantha

Reputation: 473

laravel retrieving data from database takes too much time

I am working with laravel project which uses a mysql database.It has some tables which has over 5 million data.it takes too much time to get these data to frontend. following is the function i use to get data. i have some filters used such as date range (from,to) search by name($seach) pagination amount(records_number) etc. but when i try to get all records it takes too much time. is there any solutions/optimizations for this matter?

Thanks.

public function transactionListBetween($from, $to, $sort, $search, $records_number, $filter, $previous_sort) {

    $query = $this->accountTransactions
        ->with('transactionType', 'giver', 'recipient')
        ->leftJoin('tbdb_users as recipient', 'recipient.id', '=', 'tbdb_account_transaction.recipient_id')
        ->leftJoin('tbdb_users as giver', 'giver.id', '=', 'tbdb_account_transaction.giver_id')
        ->leftJoin('tbdb_account_transaction_type', 'tbdb_account_transaction.account_transaction_type_id', '=', 'tbdb_account_transaction_type.id')
        ->where('recipient.name', 'like', "%$search%")
        ->select('tbdb_account_transaction.*');

    if($filter) {
        if($filter == -1) {
            $transactionTypeModel = \App::make('App\Models\AccountTransactionTypeModel');
            $depositTypeIds = $transactionTypeModel->whereIn('name', ['PayPal Deposit', 'Eway Deposit', 'Bank Deposit', 'BPay Deposit', 'Poli Deposit'])->lists('id');
            $query = $query->whereIn('tbdb_account_transaction.account_transaction_type_id', $depositTypeIds);
        } else {
            $query = $query->where('tbdb_account_transaction.account_transaction_type_id', $filter);
        }
    }

    if($from) {
        $query = $query->where('tbdb_account_transaction.created_date', '>=', $from);
    }

    if($to) {
        $query = $query->where('tbdb_account_transaction.created_date', '<=', $to->endOfDay());
    }

    if($records_number == 'no_paginate') {
        return $query->orderBy($sort, $previous_sort)
            ->get();
    } else {
        $totalAmount = $query->sum('amount');
        $collection = $query->orderBy($sort, $previous_sort)->paginate($records_number);
        $collection->totalAmount = number_format($totalAmount / 100, 2);
        return $collection;
    }

}

laravel debugger screenshot

Upvotes: 1

Views: 2040

Answers (3)

Deemantha
Deemantha

Reputation: 473

Found out why this is happening. it is because of pagination. it takes lot of time to paginate. there are more than 50000 pages so it takes time. i used simplePaginate() instead of paginate(). then it loads faster than usual

Upvotes: 1

Rakesh Kumar
Rakesh Kumar

Reputation: 51

In my case i have also having this problem when using mysql database. These much records in table with relationship takes too much time. You have to use raw query to get result. You have to use pagination to show the data. because fetching all the data will slow down.

But i highly recommended you to normalize your table structure. In my case i did normalize of my tables by split it with yearly.

Upvotes: 1

apokryfos
apokryfos

Reputation: 40720

You seem to be doing 3 left joins and getting the same data as you did with the with.

You can drop them:

public function transactionListBetween($from, $to, $sort, $search, $records_number, $filter, $previous_sort) {
    $query = $this->accountTransactions->->with('transactionType', 'giver', 'recipient')
                ->whereHas('recipient', function ($query) use ($search) { //Query the related model
                     $query->where('name', 'LIKE', "%$search%"); //Note this will not use an index
                 });

    if ($filter) {
       if ($filter == -1) {            
             $query->whereHas('transactionType', function ($query) {
                 $query->whereIn('id', \DB::raw("(SELECT id FROM tbdb_account_transaction_type WHERE name IN ('PayPal Deposit', 'Eway Deposit', 'Bank Deposit', 'BPay Deposit', 'Poli Deposit')");
             });
       } else {
            $query->whereHas('transactionType', function ($query) use ($filter) {
               $query->where('id', $filter);
            });
       }

    }


    if($from) {
        $query = $query->where('created_date', '>=', $from);
    }

    if($to) {
        $query = $query->where('created_date', '<=', $to->endOfDay());
    }

    if($records_number == 'no_paginate') {
        return $query->orderBy($sort, $previous_sort)
            ->get();
    } else {
        $totalAmount = $query->sum('amount'); 
        $collection = $query->orderBy($sort, $previous_sort)->paginate($records_number);
        $collection->totalAmount = number_format($totalAmount / 100, 2);
        return $collection;
    }

 }

This will reduce the main result set greatly making it easier to work with.

Upvotes: 1

Related Questions