Slow performance when use group by in query

[leakena]

Summary of problem or feature request

Hi all, this package is awesome during my development project. Currently, my table have over 2 millions records and face problem with loading data very slow around 5-10mn when I click on pagination while I use group by in my query. Could you please help me?

Code snippet of problem

    $query = DB::table('med_reports')
            ->select('refer_to','name','unit')
            ->selectRaw("CASE WHEN payment_type is null THEN 'General' ELSE 
                     payment_type END as payment_type")
            ->selectRaw("SUM(quantity) as total")
            ->groupBy('refer_to', 'name', 'unit', 'payment_type');

    return $this->datatables
        ->queryBuilder($query)
        ->filter(function ($query) {
            if($search = Input::get('search')['value']){
                $this->mapColumnSearch($query, $search);
            }
            
            $from = !empty(Input::get('from'))?Carbon::createFromFormat('d/m/Y', Input::get('from'))->format('Y-m-d 00:00:00'):date('Y-m-01 00:00:00');
            $to = !empty(Input::get('to'))?Carbon::createFromFormat('d/m/Y', Input::get('to'))->format('Y-m-d 23:59:59'):null;
            $specialist = Input::get('filter_specialist');
            $payment_type = Input::get('filter_insurance');
            
            if($from) $query->whereDate('created_at','>=', $from);
            if($to) $query->whereDate('created_at','<=', $to);
            if($specialist) $query->where('refer_to', $specialist);
            if($payment_type == 'general') $query->whereNull('payment_type_id');
            elseif($payment_type) $query->where('payment_type_id', $payment_type);
        })
        ->make(true);

System details

• Operating System Ubuntu 20
• PHP Version 7.1.33
• Laravel Version 5.2
• Laravel-Datatables Version 6.29.3

[yajra]

I suggest you install laravel-debugbar to be able to trace which query takes too much time to execute. From there, you can perform some fine tuning on your SQL.

Also, double check that you don't have a N+1 issue.

BTW, what database are you using?