Stock view optimization
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.