相同MySQL查询仅item_id不同却生成不同执行计划的问题求助

我遇到一个MySQL问题：两条结构完全一致的查询，仅末尾的item_id参数不同，但执行explain/analyze时返回的执行计划完全不同，导致查询耗时差异极大。

查询语句如下（由Laravel/Eloquent生成）：

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;

当使用某些item_id时，查询对应一种执行计划；修改为其他item_id时，会切换到另一种执行计划。这种差异呈现随机性：约30%的item_id对应较快的执行计划，70%对应较慢的。且数据库中各item的相关数据量几乎一致，我已清空查询缓存，但问题依旧。

更新内容（2023年1月21日19:04） #

发现MySQL对未在关联WHERE子句中定义的字段排序存在性能问题——此处关联表为中间表orders_items。将order by id替换为order by orders_items.order_id后，查询速度提升了10倍。

内容的提问来源于stack exchange，提问作者Dominic Olearczyk