The "Stock" view of Products can become very slow when the user selects a section or a node, and takes dozens of seconds, or more. This happened on 2 of my projects.

This is what the "WHERE" statement of the query looks like, and how Mariadb's ordonancer plans it:

WHERE
  stock.uid = catalog.uid
  AND ((stock.ledger_uid = 2809240927
  AND `related_stock_node_category_uid_1_category`.`category_uid` = 1833756305
  AND stock.resource_uid = 84476002
  AND stock.simulation_state IN ('ordered', 'planned', 'started', 'ready', 'confirmed', 'assigned', 'delivered', 'stopped', 'acknowledged')
  AND `related_stock_section_category_uid_1_category`.`category_uid` = 33761)
  AND (`stock`.`uid` = `catalog`.`uid`)
  AND (related_stock_node_category_uid_1_category.uid = stock.node_uid)
  AND (related_stock_section_category_uid_1_category.uid = stock.section_uid))

+------+-------------+-----------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------------------------------------------+------+----------------------------------------------+
| id   | select_type | table                                         | type   | possible_keys                                                                                                                                                                         | key               | key_len | ref                                                                 | rows | Extra                                        |
+------+-------------+-----------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------------------------------------------+------+----------------------------------------------+
|    1 | SIMPLE      | related_stock_node_category_uid_1_category    | ref    | PRIMARY,Membership                                                                                                                                                                    | Membership        | 8       | const                                                               |   26 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | stock                                         | ref    | PRIMARY,node_uid,resource_node_uid,resource_section_node_uid,state_section_increase_node_date,section_uid_portal_type_mirror_section_uid,ledger_uid,ledger_section_grouping_date_node | resource_node_uid | 18      | const,erp5.related_stock_node_category_uid_1_category.uid           |   40 | Using where                                  |
|    1 | SIMPLE      | related_stock_section_category_uid_1_category | ref    | PRIMARY,Membership                                                                                                                                                                    | PRIMARY           | 16      | erp5.stock.section_uid,const                                        |    1 | Using index                                  |
|    1 | SIMPLE      | catalog                                       | eq_ref | PRIMARY                                                                                                                                                                               | PRIMARY           | 8       | erp5.stock.uid                                                      |    1 |                                              |
+------+-------------+-----------------------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------------------------------------------+------+----------------------------------------------+

There are 4 steps, the 1st one being on a join table, so it can't make good use of the index. Which is sad as resource_section_node_uid looks perfect for us.

As this hints to joins making the query slow, I wrote this patch (and re-edited for submission to generic) to query first the UIDs that are searched by joins (iow: entities used as node or source of stock entries), and inject them directly in the stock query.

WHERE
  stock.uid = catalog.uid
  AND ((stock.ledger_uid = 2809240927
  AND stock.simulation_state IN ('ordered', 'planned', 'started', 'ready', 'confirmed', 'assigned', 'delivered', 'stopped', 'acknowledged')
  AND stock.resource_uid = 84476002
  AND stock.node_uid = 33883
  AND stock.section_uid IN (33883, 11452967, 11452968, 11452969, 11452970, 11452971, 11452972, 11452973, 11452974, 11452975, 11452976, 11452977, 11452978, 11452979, 11452983, 12896399, 475678011, 475678012, 475678013, 475678014, 860163658, 860163659, 860163660, 860163661, 860163662, 860163663, 860163664, 860163665, 860163666, 860163667, 860163668, 1424009821, 1491920240, 1493437626, 1567513548, 1754246652, 2136977055, 2230443736, 2244740712, 2589384676, 2621144387, 2762595449))
  AND (`stock`.`uid` = `catalog`.`uid`))

+------+-------------+---------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table   | type   | possible_keys                                                                                                                                                                         | key        | key_len | ref                      | rows | Extra                                                               |
+------+-------------+---------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | stock   | range  | PRIMARY,node_uid,resource_node_uid,resource_section_node_uid,state_section_increase_node_date,section_uid_portal_type_mirror_section_uid,ledger_uid,ledger_section_grouping_date_node | ledger_uid | 18      | NULL                     | 7495 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | catalog | eq_ref | PRIMARY                                                                                                                                                                               | PRIMARY    | 8       | erp5.stock.uid           |    1 |                                                                     |
+------+-------------+---------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------+------+---------------------------------------------------------------------+

In my case, this query becomes instant on production.

The main drawback I see is that if the list of nodes or sections is too long, which isn't the case in my projects as they represent the physical warehouse & shops of my customer, it would probably impact the query.