Questions & Answers

Two same mysql queries with different execution plans

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_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!!

Answers(1) :

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.

2023-01-20 23:30:03
Thank you for your answer! I changed the "id" to "orders_items.order_id" since mysql seem to have a problem with ordering by columns which are not included in the where clause. now the query runs almost 10 times faster...