laravel how to optimize a simple where query in one table

Question

i have a simple where query that repeats in a foreach for some times that can be a lot really so here is my query :

    for ($i = 0; $i < count($hasdate); $i++) {
        $roomprice = RoomPricingHistory::
             Where('accommodation_room_id', $hasroom[$i])
            ->where('from_date', '<=', $hasdate[$i])
            ->where('to_date', '>=', $hasdate[$i])
            ->get()->sortBy('created_at');

        $lastget = last($roomprice);
        $last_price = last($lastget);
        if ($last_price) {
            $final_price[] = $last_price->sales_price;
        } else {
            $has_not_capacity = $hasdate[$i];
        }
    }

so each time this runs it takes a bout 2,509.10ms in telescope and here is what the telescope shows me as the query which is running on table

  select
  *
from
  `room_pricing_histories`

    where
      `accommodation_room_id` = 3
      and `from_date` <= "2019-06-01 09:00:00"
      and `to_date` >= "2019-06-01 09:00:00"

so any idea on how to optimize this query ??

How big is this room_pricing_histories table? Is there already a multi-column index on the following columns: (accommodation_room_id, from_date, to_date) ? dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html dev.mysql.com/doc/refman/8.0/en/create-index.html — abelito
– abelito, Commented May 26, 2019 at 13:10

senty · Accepted Answer · 2019-05-26 12:34:08Z

1

Well, as a rule of thumb - don't run a query inside a loop.

You can use whereIn() for the querying multiple IDs

$roomIds = $hasroom // assume this has array of ids

$roomprice = RoomPricingHistory::
        whereIn('accommodation_room_id', $roomIds)
        ->where('from_date', '<=', $fromDate)
        ->where('to_date', '>=', $toDate)
        ->get()->sortBy('created_at');

answered May 26, 2019 at 12:34

senty

12.9k31 gold badges142 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

laravel how to optimize a simple where query in one table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related