PHP: Laravel query optimization

Question

I have this query that is querying through a table of more than 3 hundred thousands records. So here is the query in Laravel 5.5:

$this->select = \DB::table('transport_orders')
   ->join('users', 'users.id', '=', 'transport_orders.user_id', 'left')
   ->join('transport_order_price_requests', 'transport_order_price_requests.transport_order_id', '=', 'transport_orders.id', 'left')
   ->join('price_requests', 'transport_order_price_requests.price_request_id', '=', 'price_requests.id', 'left')
   ->join('order_numbers', 'transport_orders.order_number_id', '=', 'order_numbers.id', 'left')
   ->join(\DB::raw('
                (select transport_order_statuses.transport_order_id, max(transport_order_statuses.id) as last_link_id
                from transport_order_statuses
                group by transport_order_statuses.transport_order_id) as last_statuses'
            ), function ($join) {
     $join->on('transport_orders.id', '=', 'last_statuses.transport_order_id');
            })
   ->join('transport_order_statuses', 'last_statuses.last_link_id', '=', 'transport_order_statuses.id')
   ->join('statuses', 'transport_order_statuses.status_id', '=', 'statuses.id')
   ->select(
        'transport_orders.id as id',
             \DB::raw('CONCAT(users.first_name, \' \', users.last_name) as user_full_name'),
              \DB::raw('DATE_FORMAT(transport_orders.pickup_date , "%Y-%m-%d") as pickup_date'),
         'transport_orders.location_name',
         'transport_orders.carrier_name',
         'transport_orders.country_name',
         'transport_orders.country_code',
         'transport_orders.category_name',
         'transport_orders.zip',
         'transport_orders.rate_type',
         'transport_orders.rate_name',
         'transport_orders.order_number_id',
         'order_numbers.order_number as order_number',
         'transport_orders.comment',
         'transport_orders.inbound',
         'transport_orders.unit',
         'transport_orders.unit_value',
         \DB::raw('DATE_FORMAT(transport_orders.created_at , "%Y-%m-%d") as created_at'),
         'transport_orders.base_price',
         'transport_orders.created_at',
         'transport_orders.updated_at',
         'transport_orders.price',
         'transport_orders.stops',
         'transport_orders.stop_price',
         'transport_orders.id as id',
         'transport_orders.transport_mode as transport_mode_name',
         'statuses.status as status',
         'statuses.id as status_id',
         'transport_orders.multishipment as multishipment',
         'transport_orders.total_weight as total_weight',
         'order_numbers.simulation as simulated'
 )->where('transport_orders.is_deleting', '=', 0);

And after this I do:

$this->select->paginate($itemsPerPage);

Now if this query is to be converted to plain MySql, here it is:

select  `transport_orders`.`id` as `id`,
CONCAT(users.first_name, ' ', users.last_name) as user_full_name,
DATE_FORMAT(transport_orders.pickup_date , "%Y-%m-%d") as pickup_date,
`transport_orders`.`location_name`, `transport_orders`.`carrier_name`,
`transport_orders`.`country_name`, `transport_orders`.`country_code`,
`transport_orders`.`category_name`, `transport_orders`.`zip`,
`transport_orders`.`rate_type`, `transport_orders`.`rate_name`,
`transport_orders`.`order_number_id`, `order_numbers`.`order_number` as `order_number`,
`transport_orders`.`comment`, `transport_orders`.`inbound`, `transport_orders`.`unit`,
`transport_orders`.`unit_value`,
DATE_FORMAT(transport_orders.created_at , "%Y-%m-%d") as created_at,
`transport_orders`.`base_price`, `transport_orders`.`created_at`,
`transport_orders`.`updated_at`, `transport_orders`.`price`, `transport_orders`.`stops`,
        `transport_orders`.`stop_price`, `transport_orders`.`id` as `id`,
        `transport_orders`.`transport_mode` as `transport_mode_name`,
        `statuses`.`status` as `status`, `statuses`.`id` as `status_id`,
        `transport_orders`.`multishipment` as `multishipment`,
        `transport_orders`.`total_weight` as `total_weight`, `order_numbers`.`simulation` as `simulated`
    from  `transport_orders`
    left join  `users`  ON `users`.`id` = `transport_orders`.`user_id`
    left join  `transport_order_price_requests`  ON `transport_order_price_requests`.`transport_order_id` = `transport_orders`.`id`
    left join  `price_requests`  ON `transport_order_price_requests`.`price_request_id` = `price_requests`.`id`
    left join  `order_numbers`  ON `transport_orders`.`order_number_id` = `order_numbers`.`id`
    inner join  
    (
        SELECT  transport_order_statuses.transport_order_id, max(transport_order_statuses.id) as last_link_id
            from  transport_order_statuses
            group by  transport_order_statuses.transport_order_id
    ) as last_statuses  ON `transport_orders`.`id` = `last_statuses`.`transport_order_id`
    inner join  `transport_order_statuses`  ON `last_statuses`.`last_link_id` = `transport_order_statuses`.`id`
    inner join  `statuses`  ON `transport_order_statuses`.`status_id` = `statuses`.`id`
    where  `transport_orders`.`is_deleting` = 0
      and  `order_numbers`.`simulation` = 0
    order by  `transport_orders`.`created_at` desc
    limit  13 offset 0

The Laravel debugbar tells me that the query is taking more than 9 seconds. As I am using paginate (Length Aware Paginator), it also does a count so that takes another around 6 seconds. When I run the plain query in MySql it takes about 9-10 seconds as well. How can I optimize this query? Note that I don't want to use simplePaginate as it would require a whole lot of refactoring.

Just to observe, dates are "%Y-%m-%d", so DATE_FORMAT(..."%Y-%m-%d") is either redundant, or identical to DATE(...) — Strawberry
– Strawberry, Commented Mar 3, 2021 at 8:28

Rick James · Accepted Answer · 2021-03-03 17:28:02Z

1

These indexes may help:

transport_order_statuses:  (transport_order_id, id)
transport_orders:  (is_deleting, created_at)
transport_order_price_requests:  (transport_order_id, price_request_id)

Changing all the LEFT JOINs into JOINs may provide the same answer while giving the Optimizer more options (and confuse me less).

Because two tables are referenced in WHERE, the query probably has to be evaluated completed through the WHERE before getting to the ORDER BY and LIMIT, there not allowing any optimizations in this area. Getting rid of simulation = 0 may speed things up (at the cost of getting too many rows).

answered Mar 3, 2021 at 17:28

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

Collectives™ on Stack Overflow

PHP: Laravel query optimization

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