bad 10.5 commit query plan eb483c5181ab430877c135c16224284cfc517b3d +------+-------------+------------+--------+--------------------------------------------------------------------------------------------------+----------------------+---------+------------------------+-------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+--------+--------------------------------------------------------------------------------------------------+----------------------+---------+------------------------+-------+----------+----------------------------------------------+ | 1 | PRIMARY | d2_ | const | PRIMARY | PRIMARY | 16 | const | 1 | 100.00 | Using index; Using temporary; Using filesort | | 1 | PRIMARY | s3_ | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 13 | 0.00 | Using where | | 1 | PRIMARY | d4_ | ref | PRIMARY,IDX_9E5EF6FD4584665A,IDX_9E5EF6FD9DAFEFE6 | IDX_9E5EF6FD9DAFEFE6 | 4 | const | 71488 | 100.00 | Using index | | 1 | PRIMARY | d1_ | ref | PRIMARY,IDX_internalAmount | IDX_internalAmount | 4 | subq.internal_amount_2 | 566 | 100.00 | | | 1 | PRIMARY | d0_ | ref | PRIMARY,UNIQ_producer_code,IDX_25A6FEBF89B658FE,IDX_25A6FEBF79066886,IDX_25A6FEBFDCD6110,visible | UNIQ_producer_code | 17 | const | 13 | 100.00 | Using index condition; Using where | | 2 | DERIVED | d2_ | const | PRIMARY | PRIMARY | 16 | const | 1 | 100.00 | Using index; Using temporary; Using filesort | | 2 | DERIVED | s3_ | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 2 | DERIVED | d0_ | ref | PRIMARY,UNIQ_producer_code,IDX_25A6FEBF89B658FE,IDX_25A6FEBF79066886,IDX_25A6FEBFDCD6110,visible | IDX_25A6FEBF89B658FE | 17 | const | 13 | 100.00 | Using index condition; Using where | | 2 | DERIVED | d1_ | eq_ref | PRIMARY,IDX_internalAmount | PRIMARY | 16 | test.d0_.stock_id | 1 | 1.27 | Using where | | 2 | DERIVED | d4_ | eq_ref | PRIMARY,IDX_9E5EF6FD4584665A,IDX_9E5EF6FD9DAFEFE6 | PRIMARY | 20 | test.d0_.id,const | 1 | 100.00 | Using index | +------+-------------+------------+--------+--------------------------------------------------------------------------------------------------+----------------------+---------+------------------------+-------+----------+----------------------------------------------+ 11 rows in set, 1 warning (0.025 sec) show warnings; | Note | 1003 | /* select#1 */ select `test`.`d0_`.`id` AS `id_0`,`test`.`d0_`.`root_id` AS `sclr_1`,`test`.`d1_`.`internal_amount` AS `internal_amount_2` from `test`.`d_product` `d0_` join `test`.`d_product_stock` `d1_` join `test`.`d_producer` `d2_` join `test`.`d_product_categories_all` `d4_` join `test`.`structure_node` `s3_` join (/* select#2 */ select `test`.`d0_`.`root_id` AS `root_id`,`test`.`d0_`.`id` AS `id_0`,`test`.`d0_`.`root_id` AS `sclr_1`,max(`test`.`d1_`.`internal_amount`) AS `internal_amount_2` from `test`.`d_product` `d0_` join `test`.`d_product_stock` `d1_` join `test`.`d_producer` `d2_` join `test`.`d_product_categories_all` `d4_` join `test`.`structure_node` `s3_` where `test`.`d0_`.`visible` = 1 and `test`.`d4_`.`structure_node_id` = 7 and `test`.`d0_`.`producer_id` = 0x90e7ac1f6bf1f375 and `test`.`d4_`.`product_id` = `test`.`d0_`.`id` and `test`.`d1_`.`id` = `test`.`d0_`.`stock_id` and `test`.`d1_`.`internal_amount` > 0 group by `test`.`d0_`.`root_id`) `subq` where `test`.`d1_`.`internal_amount` = `subq`.`internal_amount_2` and `test`.`d0_`.`visible` = 1 and `test`.`d4_`.`structure_node_id` = 7 and `test`.`d0_`.`producer_id` = 0x90e7ac1f6bf1f375 and `test`.`d0_`.`root_id` = `subq`.`root_id` and `test`.`d0_`.`id` = `test`.`d4_`.`product_id` and `test`.`d0_`.`stock_id` = `test`.`d1_`.`id` and `subq`.`internal_amount_2` > 0 group by `test`.`d0_`.`root_id` order by `test`.`d0_`.`purchasable_variant` desc,`test`.`d1_`.`internal_amount_2` desc,`test`.`d0_`.`id` |