vendredi 23 septembre 2016

Laravel 5.2 Eloquent query takes too long time to 'count'

I am using Laravel 5.2 with MySQL 5.6.33

When I check the DB query by applying dd(DB::getQueryLog()); , it returns as following:

array:14 [▼
  0 => array:3 [▼
  "query" => "select count(*) as aggregate from `products` where exists (select * from `agents` where `products`.`agent_id` = `agents`.`id`) and exists (select * from `productlocations` where `productlocations`.`product_id` = `products`.`id` and `town_id` = ?) and exists (select * from `productagents` where `productagents`.`product_id` = `products`.`id` and `product_status_id` = ?)"
  "bindings" => array:2 [▼
        0 => "674"
        1 => "1"
  ]
  "time" => 22286.26
  ]

  1 => array:3 [▼
  "query" => "select * from `products` where exists (select * from `agents` where `products`.`agent_id` = `agents`.`id`) and exists (select * from `productlocations` where `productlocations`.`product_id` = `products`.`id` and `town_id` = ?) and exists (select * from `productagents` where `productagents`.`product_id` = `products`.`id` and `product_status_id` = ?) order by `id` desc limit 10 offset 0"
  "bindings" => array:2 [▼
        0 => "674"
        1 => "1"
  ]
  "time" => 38.4
  ]

  2 => array:3 [▼
  "query" => "select * from `agents` where `agents`.`id` in (?)"
  "bindings" => array:1 [▶]
  "time" => 0.58
]

As you can see the result, the "count" query has taken 22286ms to complete , but the same query of filtering data has took only 38.4ms. Where I need to look at to make faster this query? Need to change in the DB or Eloquent?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire