John Davidson

Message:

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.

---

My Answer:
Here are a few suggestions to optimize your query:

1. Indexing: Make sure that the columns you are joining on and filtering by are indexed in your database. This can significantly improve the performance of your query.

2. Limit the number of columns selected: Only select the columns that you actually need in your result set. This can reduce the amount of data that needs to be retrieved from the database.

3. Use eager loading: If you are fetching related models, consider using eager loading to reduce the number of queries being executed. This can be done using the `with()` method in Laravel.

4. Use raw queries sparingly: Avoid using raw queries unless absolutely necessary. Laravel's query builder provides a more secure and readable way to build queries.

5. Consider optimizing the subquery: The subquery in your query may be causing performance issues. You can try optimizing it or finding an alternative way to achieve the same result.

6. Use caching: Consider caching the results of your query if they are not frequently changing. This can reduce the load on your database server.

7. Use pagination wisely: If possible, try to limit the number of records being fetched at a time. This can improve the performance of your query.

By implementing these optimizations, you should be able to improve the performance of your query in Laravel.