Also if I remove group by then the execution time drops to 15 ms
Apr 23, 2019
11
Level 14
Query takes 10 seconds if I add orderBy and paginate
I have this query but it is taking 10 seconds to execute. If I remove orderByRaw nad paginate then it only takes 15 ms. But I need to order it and paginate it. Is there another way to do it?
$inkasso = \App\Models\Inkasso::selectRaw(implode(',', $columns))
->leftJoin('inkasso_items', 'inkasso.id', '=', 'inkasso_items.ink_id')
->leftJoin('end_statuses', 'inkasso.end_status', '=', 'end_statuses.id')
->leftJoin('inkasso_statuss', function($join) {
$join->on(function($query) {
$query->on('inkasso.status', '=', 'inkasso_statuss.id');
$query->orOn('inkasso.parent_status', '=', 'inkasso_statuss.id');
});
})
->leftJoin('companies', function($join) {
$join->on(function($query) {
$query->on('inkasso.parent_id', '=', 'companies.id');
$query->orOn('inkasso.grand_parent_id', '=', 'companies.id');
});
})
->orderByRaw(
$this->getOrderBy($options->orderBy, $options->orderDirection)
)
->take($options->perPage)
->groupBy('inkasso.id')
->paginate(
$options->perPage, // per page
['*'], // columns
'page', // page query variable name
$options->page // current page
);
Here are the queries executed from debugbar
select count(*) as aggregate from "inkasso" left join "inkasso_items" on "inkasso"."id" = "inkasso_items"."ink_id" left join "end_statuses" on "inkasso"."end_status" = "end_statuses"."id" left join "inkasso_statuss" on ("inkasso"."status" = "inkasso_statuss"."id" or "inkasso"."parent_status" = "inkasso_statuss"."id") left join "companies" on ("inkasso"."parent_id" = "companies"."id" or "inkasso"."grand_parent_id" = "companies"."id") group by "inkasso"."id"
3.94s
select inkasso.id,inkasso.i_date,inkasso.end_date,inkasso.full_name,inkasso.status,inkasso.parent_status,inkasso.end_status,inkasso.debt_sum,inkasso.pay_sum,inkasso.rest_sum,inkasso.company,inkasso.parent_id,inkasso.grand_parent_id,inkasso.pers_code,inkasso.ink_number,inkasso.ink_sum,inkasso.employer,inkasso.credit_level,inkasso.note,COALESCE(SUM(inkasso_items.ink_sum), 0.00) as paid_ink,COALESCE(COUNT(inkasso_items), 0) as pay_count from "inkasso" left join "inkasso_items" on "inkasso"."id" = "inkasso_items"."ink_id" left join "end_statuses" on "inkasso"."end_status" = "end_statuses"."id" left join "inkasso_statuss" on ("inkasso"."status" = "inkasso_statuss"."id" or "inkasso"."parent_status" = "inkasso_statuss"."id") left join "companies" on ("inkasso"."parent_id" = "companies"."id" or "inkasso"."grand_parent_id" = "companies"."id") group by "inkasso"."id" order by end_date desc NULLS LAST limit 10 offset 0
4.45s
Please or to participate in this conversation.
