laravel retrieving data from database takes too much time

laravel db
laravel union
laravel wheredate
where like laravel
laravel exists
laravel where between
order by in laravel
laravel distinct

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;
    }

}

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.

php, 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  Laravel - Retrieve Records - After configuring the database, we can retrieve the records using the DB facade with select method. The syntax of select method is as shown in the following tab

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.

Query on my model taking too much time to finish., $data = TopFiber::select(DB::raw('UserName, sum(AcctInputOctets) as Input But this is taking too much time like 5 minutes to complete or so. Can i do instead of get(); do toSql() to dump out the query and post it here (and also run in mysql directly) Proudly hosted with Laravel Forge and DigitalOcean. Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems. The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks.

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

Laravel relation query taking too much time to load, Hello, I have two models, one for school Member and the other for school Attendance, each one with the corresponding database table. My goal is to get There are two primary ways of working with session data in Laravel: the global session helper and via a Request instance. First, let's look at accessing the session via a Request instance, which can be type-hinted on a controller method.

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  The Eloquent ORM included with Laravel provides a beautiful, simple ActiveRecord implementation for working with your database. Each database table has a corresponding "Model" which is used to interact with that table. Before getting started, be sure to configure a database connection in config/database.php. To get started, create an Eloquent

Optimizing Laravel Part 2: Improving Query Performance with , A database index improves the speed of retrieving data from a table by maintaining an “index” of the data No wonder the query takes so long. composer require tamkeenlms/laravel-data-selector Now, for each model you have you will create a Selector class, one that will extend DataSelector\Selector, and then you will get to call this selector and ask it for x or y. I suggest you create a new directory for your selectors under /app.

My function takes too much time to execute ????, //route to the dashboard Route::get('dashboard', [ 'uses' All your data is stored in the database, and you have to ask the database to give you  This ensures that any data written during the request cycle can be immediately read back from the database during that same request. It is up to you to decide if this is the desired behavior for your application.

Comments
  • First, are you using indexing on tables?
  • @LovepreetSingh yes i use
  • You have a model query and a with and then 3 left joins. Why not just 4 relationships on the model?
  • i didn't get you mate @apokryfos
  • I think Eloquent relationship's more slower in this scenario. It is database issue. In my opinion u should get generated SQL from this code and check Execution Plan in database.
  • Tried your solution but having same problem mate.
  • If removing 3 left joins from the query has not reduced the query time then perhaps the problem is not the query but the actual volume of data you're trying to present
  • yes it has over 5 million records. when i try to get few records it works fine
  • How is it useful to throw 5 million records to a user?
  • its useless to deliver 5Mn. but lets say for 10 000 records. it occurs same problem,