good 10.4 commit query plan b3ab3105fdb34dae6c2d4270751bc0694c3d9df8
+------+-----------------+------------+------------+--------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------------------+--------+----------+----------------------------------------------+
| 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         | 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           |
|    1 | PRIMARY         | d1_        | eq_ref     | PRIMARY,IDX_internalAmount                                                                       | PRIMARY                                   | 16      | test.d0_.stock_id | 1      |     1.27 | Using where                                  |
|    1 | PRIMARY         | d4_        | eq_ref     | PRIMARY,IDX_9E5EF6FD4584665A,IDX_9E5EF6FD9DAFEFE6                                                | PRIMARY                                   | 20      | test.d0_.id,const | 1      |   100.00 | Using index                                  |
|    1 | PRIMARY         | <derived2> | ref        | key0                                                                                             | key0                                      | 17      | test.d0_.root_id  | 2      |   100.00 | Using where                                  |
|    2 | LATERAL DERIVED | d2_        | const      | PRIMARY                                                                                          | PRIMARY                                   | 16      | const             | 1      |   100.00 | Using index                                  |
|    2 | LATERAL DERIVED | s3_        | const      | PRIMARY                                                                                          | PRIMARY                                   | 4       | const             | 1      |   100.00 | Using index                                  |
|    2 | LATERAL DERIVED | d0_        | ref|filter | PRIMARY,UNIQ_producer_code,IDX_25A6FEBF89B658FE,IDX_25A6FEBF79066886,IDX_25A6FEBFDCD6110,visible | IDX_25A6FEBF79066886|IDX_25A6FEBF89B658FE | 17|17   | test.d0_.root_id  | 4 (0%) |    50.00 | Using where; Using rowid filter              |
|    2 | LATERAL DERIVED | d1_        | eq_ref     | PRIMARY,IDX_internalAmount                                                                       | PRIMARY                                   | 16      | test.d0_.stock_id | 1      |     1.27 | Using where                                  |
|    2 | LATERAL 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.026 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`.`d1_`.`internal_amount` > 0 and `test`.`d0_`.`root_id` = `test`.`d0_`.`root_id` and `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` group by `test`.`d0_`.`root_id` having `internal_amount_2` > 0) `subq` where `subq`.`internal_amount_2` = `test`.`d1_`.`internal_amount` and `test`.`d0_`.`visible` = 1 and `test`.`d4_`.`structure_node_id` = 7 and `test`.`d0_`.`producer_id` = 0x90e7ac1f6bf1f375 and `subq`.`root_id` = `test`.`d0_`.`root_id` 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` order by `test`.`d0_`.`purchasable_variant` desc,`test`.`d1_`.`internal_amount_2` desc,`test`.`d0_`.`id` |