Question 16132 in Laravel Question posted in
The official Laravel Documentation can be found here.

Two same mysql queries with different execution plans – Laravel

I am struggling with a mysql problem.

I have two exact same queries, just the item_id at the end is different, but they return different execution plans when I execute them with analyze/explain.

This results in a huge difference of time needed to return a result.

The query is something like

explain select `orders`.*, 
 (select count(*) from `items` 
  inner join `orders_items` on `items`.`id` = `orders_items`.`item_id` 
  where `orders`.`id` = `orders_items`.`order_id` 
   and `items`.`deleted_at` is null) as `items_count`, 
 (select count(*) from `returns` 
  where `orders`.`id` = `returns`.`order_id` 
   and `returns`.`deleted_at` is null) as `returns_count`, 
 (select count(*) from `shippings` 
  where `orders`.`id` = `shippings`.`order_id` 
   and `shippings`.`deleted_at` is null) as `shippings_count`, 
 (select count(*) from `orders` as `laravel_reserved_2` 
  where `orders`.`id` = `laravel_reserved_2`.`recurred_from_id` 
   and `laravel_reserved_2`.`deleted_at` is null) as `recurred_orders_count`, 
 (select COALESCE(SUM(orders_items.amount), 0) from `items` 
  inner join `orders_items` on `items`.`id` = `orders_items`.`item_id` 
  where `orders`.`id` = `orders_items`.`order_id` 
  and `items`.`deleted_at` is null) as `items_sum_orders_itemsamount`, 
 `orders`.*, 
 `orders_items`.`item_id` as `pivot_item_id`, 
 `orders_items`.`order_id` as `pivot_order_id`,
 `orders_items`.`amount` as `pivot_amount`, 
 `orders_items`.`id` as `pivot_id` 
from `orders` 
inner join `orders_items` on `orders`.`id` = `orders_items`.`order_id` 
where `orders_items`.`item_id` = 497 
 and `import_finished` = 1 
 and `orders`.`deleted_at` is null 
order by `id` desc limit 50 offset 0;

As you can see it is a laravel/eloquent query.

This is the execution plan for the query above:

enter image description here

But when I change the item_id at the end it return the following execution plan

enter image description here

It is absolutely random. 30% of the item_id’s return the faster one and 70% return the slower one and I have no idea why. The related data is almost the same for every item we have in our database.

I also flushed the query cache to see if this was causing the different exec plans but no success.
I am googlin’ since 4 hours but I can’t find anything about this exact problem.

Can someone of you guys tell me why this hapens?

Thanks in advance!!

Edit 01/21/2023 – 19:04:

Seems like mysql don’t like to order by columns which are not defined in the related where clause, in this case the pivot table orders_items.

I just replaced the

order by id

with

order by orders_items.order_id

This results in a 10 times faster query.

Tags:

2

Answers


  1. 0

    A query using different execution plans just because of a different parameter can have several reasons. The simplest explanation would be the position of the used item_id in the relevant index. The position in the index may affect the cost of using the index which in turn may affect if it is used at all. (this is just an example)

    It is important to note that the explain statement will give you the planned execution plan but maybe not the actually used one.

    EXPLAIN ANALYZE is the command which will output the actually used execution plan for you. It may still yield different results for different parameters.

    Login or Signup to reply.
  2. 0 
    ON `orders`.`id` = `orders_items`.`order_id`

    where  `orders_items`.`item_id` = 497
      and  ???.`import_finished` = 1
      and  `orders`.`deleted_at` is null
    order by  ???.`id` desc
    limit  50 offset 0;

    One order maps to many order items, correct?

    So, ORDER BY orders.id is different than ORDER BY orders_items.item_id.

    Does item_id = 497 show up in many different orders?

    So, think about which ORDER BY you really want.

    Meanwhile, these may help with performance:

    orders_items:  INDEX(order_id, item_id)
returns:  INDEX(order_id, deleted_at)
shippings:  INDEX(order_id, deleted_at)
laravel_reserved_2:  INDEX(recurred_from_id, deleted_at)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search