MartinZeltin
7 months ago

Query takes 10 seconds if I add orderBy and paginate

Posted 7 months ago by MartinZeltin

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 sign in or create an account to participate in this conversation.