PHP: Laravel query optimization

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.

via Chebli Mohamed