Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

martinszeltins's avatar

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

0 likes
11 replies
martinszeltins's avatar

Also if I remove group by then the execution time drops to 15 ms

Snapey's avatar

Think about what indexes might help.

Cronix's avatar

Have you run an explain on the query to see what the bottlenecks are? (there's an option in debugbar to set that automatically although it takes even longer for queries to run to get that info)

jlrdw's avatar

Group by is always slower it has to transverse the whole result set.

It's usually used for things like a month end report.

martinszeltins's avatar

@JLRDW - Well aggregate functions don't work without group by. How could I rewrite it without it?

jlrdw's avatar

How many results do you get without pagination I mean is this a huge database.

martinszeltins's avatar

@JLRDW - It's a table with 100k records and growing fast. It seems like group by and order by in my query causes it to go from 0.15 sec to 10 sec

Tray2's avatar

Look like there are a few full table scans in this one.

I suggest you do an explain plan and look for them. Add indexes where they are it might help. I would also see which part of the query that takes the most time and place that last.

Please or to participate in this conversation.