Server version: 10.1.19-MariaDB MariaDB Server EXPLAIN SELECT `erp_view_supplyneeds_global`.* FROM `erp_view_supplyneeds_global` WHERE (`erp_view_supplyneeds_global`.`product_id`='3104'); +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+-----------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+-----------------+----------------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 136475402978376 | Using where | | 2 | DERIVED | tbl_stock | ALL | PRIMARY | NULL | NULL | NULL | 21 | Using where; Using temporary; Using filesort | | 2 | DERIVED | tbl_stock_item | ref | UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID,IDX_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID,IDX_CATALOGINVENTORY_STOCK_ITEM_STOCK_ID | IDX_CATALOGINVENTORY_STOCK_ITEM_STOCK_ID | 2 | nnpro_mwrccars_migr.tbl_stock.stock_id | 264 | Using where | | 2 | DERIVED | tbl_product | eq_ref | PRIMARY | PRIMARY | 4 | nnpro_mwrccars_migr.tbl_stock_item.product_id | 1 | Using where | | 2 | DERIVED | tbl_config_manufacturer | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | | 2 | DERIVED | tbl_waiting_for_delivery_qty | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 2 | DERIVED | tbl_manual_supply_needs | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 2 | DERIVED | tbl_manufacturer | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,nnpro_mwrccars_migr.tbl_config_manufacturer.value,const | 1 | Using where | | 2 | DERIVED | tbl_name | eq_ref | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 2 | DERIVED | tbl_config_ideal | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | | 2 | DERIVED | tbl_config_notify | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+-----------------+----------------------------------------------+ 11 rows in set (0.00 sec) EXPLAIN select `erp_view_supplyneeds_base`.`product_id` AS `product_id`,`erp_view_supplyneeds_base`.`manufacturer_id` AS `manufacturer_id`,`erp_view_supplyneeds_base`.`sku` AS `sku`,`erp_view_supplyneeds_base`.`name` AS `name`,`erp_view_supplyneeds_base`.`waiting_for_delivery_qty` AS `waiting_for_delivery_qty`,if(((sum(`erp_view_supplyneeds_base`.`qty_needed_for_valid_orders`) > 0) and (sum(`erp_view_supplyneeds_base`.`qty_needed_for_valid_orders`) > `erp_view_supplyneeds_base`.`waiting_for_delivery_qty`)),'1_valid_orders',if(((sum(`erp_view_supplyneeds_base`.`qty_needed_for_orders`) > 0) and ((sum(`erp_view_supplyneeds_base`.`qty_needed_for_orders`) - sum(`erp_view_supplyneeds_base`.`qty_needed_for_valid_orders`)) > (`erp_view_supplyneeds_base`.`waiting_for_delivery_qty` - sum(`erp_view_supplyneeds_base`.`qty_needed_for_valid_orders`)))),'2_orders',if(((sum(`erp_view_supplyneeds_base`.`qty_needed_for_ideal_stock`) > 0) and ((sum(`erp_view_supplyneeds_base`.`qty_needed_for_orders`) + sum(`erp_view_supplyneeds_base`.`qty_needed_for_ideal_stock`)) > `erp_view_supplyneeds_base`.`waiting_for_delivery_qty`)),'3_prefered_stock_level',if((sum(`erp_view_supplyneeds_base`.`qty_needed_for_manual_supply_needs`) > 0),'4_manual_supply_need','5_pending_delivery')))) AS `status`,if(((sum(`erp_view_supplyneeds_base`.`qty_needed_for_valid_orders`) - `erp_view_supplyneeds_base`.`waiting_for_delivery_qty`) > 0),(sum(`erp_view_supplyneeds_base`.`qty_needed_for_valid_orders`) - `erp_view_supplyneeds_base`.`waiting_for_delivery_qty`),0) AS `qty_min`,if(((sum(((`erp_view_supplyneeds_base`.`qty_needed_for_orders` + `erp_view_supplyneeds_base`.`qty_needed_for_ideal_stock`) + `erp_view_supplyneeds_base`.`qty_needed_for_manual_supply_needs`)) - `erp_view_supplyneeds_base`.`waiting_for_delivery_qty`) > 0),(sum(((`erp_view_supplyneeds_base`.`qty_needed_for_orders` + `erp_view_supplyneeds_base`.`qty_needed_for_ideal_stock`) + `erp_view_supplyneeds_base`.`qty_needed_for_manual_supply_needs`)) - `erp_view_supplyneeds_base`.`waiting_for_delivery_qty`),0) AS `qty_max` from `nnpro_mwrccars_migr`.`erp_view_supplyneeds_base` where ((`erp_view_supplyneeds_base`.`qty_needed_for_valid_orders` > 0) or (`erp_view_supplyneeds_base`.`qty_needed_for_orders` > 0) or (`erp_view_supplyneeds_base`.`qty_needed_for_ideal_stock` > 0) or (`erp_view_supplyneeds_base`.`qty_needed_for_manual_supply_needs` > 0)) group by `erp_view_supplyneeds_base`.`product_id`,`erp_view_supplyneeds_base`.`manufacturer_id`,`erp_view_supplyneeds_base`.`sku`,`erp_view_supplyneeds_base`.`name`; +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | tbl_stock | ALL | PRIMARY | NULL | NULL | NULL | 21 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | tbl_stock_item | ref | UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID,IDX_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID,IDX_CATALOGINVENTORY_STOCK_ITEM_STOCK_ID | IDX_CATALOGINVENTORY_STOCK_ITEM_STOCK_ID | 2 | nnpro_mwrccars_migr.tbl_stock.stock_id | 264 | Using where | | 1 | SIMPLE | tbl_product | eq_ref | PRIMARY | PRIMARY | 4 | nnpro_mwrccars_migr.tbl_stock_item.product_id | 1 | Using where | | 1 | SIMPLE | tbl_config_manufacturer | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | | 1 | SIMPLE | tbl_waiting_for_delivery_qty | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 1 | SIMPLE | tbl_manual_supply_needs | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 1 | SIMPLE | tbl_manufacturer | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,nnpro_mwrccars_migr.tbl_config_manufacturer.value,const | 1 | Using where | | 1 | SIMPLE | tbl_name | eq_ref | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 1 | SIMPLE | tbl_config_ideal | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | | 1 | SIMPLE | tbl_config_notify | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+------+----------------------------------------------+ EXPLAIN select `tbl_stock_item`.`product_id` AS `product_id`,`tbl_stock_item`.`stock_id` AS `stock_id`,`tbl_product`.`sku` AS `sku`,`tbl_name`.`value` AS `name`,`tbl_manufacturer`.`value` AS `manufacturer_id`,`tbl_stock_item`.`qty` AS `stock`,if((`tbl_stock_item`.`qty` > `tbl_stock_item`.`stock_ordered_qty`),(`tbl_stock_item`.`qty` - `tbl_stock_item`.`stock_ordered_qty`),0) AS `available_qty`,if((`tbl_stock_item`.`use_config_notify_stock_qty` = 1),cast(`tbl_config_notify`.`value` as signed),`tbl_stock_item`.`notify_stock_qty`) AS `warning_stock_level`,cast(if((`tbl_stock_item`.`use_config_ideal_stock_level` = 1),cast(`tbl_config_ideal`.`value` as signed),`tbl_stock_item`.`ideal_stock_level`) as unsigned) AS `ideal_stock_level`,if(`tbl_waiting_for_delivery_qty`.`value`,`tbl_waiting_for_delivery_qty`.`value`,0) AS `waiting_for_delivery_qty`,if(`tbl_manual_supply_needs`.`value`,`tbl_manual_supply_needs`.`value`,0) AS `manual_supply_needs_qty`,if((`tbl_stock_item`.`qty` > `tbl_stock_item`.`stock_ordered_qty_for_valid_orders`),0,(`tbl_stock_item`.`stock_ordered_qty_for_valid_orders` - `tbl_stock_item`.`qty`)) AS `qty_needed_for_valid_orders`,if((`tbl_stock_item`.`qty` > `tbl_stock_item`.`stock_ordered_qty`),0,(`tbl_stock_item`.`stock_ordered_qty` - `tbl_stock_item`.`qty`)) AS `qty_needed_for_orders`,if((if((`tbl_stock_item`.`qty` > `tbl_stock_item`.`stock_ordered_qty`),(`tbl_stock_item`.`qty` - `tbl_stock_item`.`stock_ordered_qty`),0) < if((`tbl_stock_item`.`use_config_notify_stock_qty` = 1),cast(`tbl_config_notify`.`value` as signed),`tbl_stock_item`.`notify_stock_qty`)),(if((`tbl_stock_item`.`use_config_ideal_stock_level` = 1),cast(`tbl_config_ideal`.`value` as signed),`tbl_stock_item`.`ideal_stock_level`) - if((`tbl_stock_item`.`qty` > `tbl_stock_item`.`stock_ordered_qty`),(`tbl_stock_item`.`qty` - `tbl_stock_item`.`stock_ordered_qty`),0)),0) AS `qty_needed_for_ideal_stock`,if((`tbl_manual_supply_needs`.`value` > if((`tbl_stock_item`.`qty` > `tbl_stock_item`.`stock_ordered_qty`),(`tbl_stock_item`.`qty` - `tbl_stock_item`.`stock_ordered_qty`),0)),(`tbl_manual_supply_needs`.`value` - if((`tbl_stock_item`.`qty` > `tbl_stock_item`.`stock_ordered_qty`),(`tbl_stock_item`.`qty` - `tbl_stock_item`.`stock_ordered_qty`),0)),0) AS `qty_needed_for_manual_supply_needs` from (((((((((`nnpro_mwrccars_migr`.`cataloginventory_stock_item` `tbl_stock_item` join `nnpro_mwrccars_migr`.`cataloginventory_stock` `tbl_stock` on((`tbl_stock_item`.`stock_id` = `tbl_stock`.`stock_id`))) join `nnpro_mwrccars_migr`.`catalog_product_entity` `tbl_product` on((`tbl_stock_item`.`product_id` = `tbl_product`.`entity_id`))) join `nnpro_mwrccars_migr`.`core_config_data` `tbl_config_notify` on((1 = 1))) join `nnpro_mwrccars_migr`.`core_config_data` `tbl_config_ideal` on((1 = 1))) join `nnpro_mwrccars_migr`.`core_config_data` `tbl_config_manufacturer` on((1 = 1))) left join `nnpro_mwrccars_migr`.`catalog_product_entity_int` `tbl_waiting_for_delivery_qty` on(((`tbl_waiting_for_delivery_qty`.`entity_id` = `tbl_stock_item`.`product_id`) and (`tbl_waiting_for_delivery_qty`.`attribute_id` = 180) and (`tbl_waiting_for_delivery_qty`.`store_id` = 0)))) left join `nnpro_mwrccars_migr`.`catalog_product_entity_int` `tbl_manual_supply_needs` on(((`tbl_manual_supply_needs`.`entity_id` = `tbl_stock_item`.`product_id`) and (`tbl_manual_supply_needs`.`attribute_id` = 177) and (`tbl_manual_supply_needs`.`store_id` = 0)))) left join `nnpro_mwrccars_migr`.`catalog_product_entity_int` `tbl_manufacturer` on(((`tbl_manufacturer`.`entity_id` = `tbl_stock_item`.`product_id`) and (`tbl_manufacturer`.`attribute_id` = `tbl_config_manufacturer`.`value`) and (`tbl_manufacturer`.`store_id` = 0)))) left join `nnpro_mwrccars_migr`.`catalog_product_entity_varchar` `tbl_name` on(((`tbl_name`.`entity_id` = `tbl_stock_item`.`product_id`) and (`tbl_name`.`attribute_id` = 65) and (`tbl_name`.`store_id` = 0)))) where ((`tbl_stock`.`stock_disable_supply_needs` <> 1) and ((`tbl_stock_item`.`use_config_manage_stock` = 1) or (`tbl_stock_item`.`manage_stock` = 1)) and (`tbl_product`.`exclude_from_supply_needs` = 0) and (`tbl_config_notify`.`path` = 'cataloginventory/item_options/notify_stock_qty') and (`tbl_config_ideal`.`path` = 'advancedstock/prefered_stock_level/ideal_stock_default_value') and (`tbl_config_manufacturer`.`path` = 'purchase/supplyneeds/manufacturer_attribute')); +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+------+-------------+ | 1 | SIMPLE | tbl_stock | ALL | PRIMARY | NULL | NULL | NULL | 21 | Using where | | 1 | SIMPLE | tbl_stock_item | ref | UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID,IDX_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID,IDX_CATALOGINVENTORY_STOCK_ITEM_STOCK_ID | IDX_CATALOGINVENTORY_STOCK_ITEM_STOCK_ID | 2 | nnpro_mwrccars_migr.tbl_stock.stock_id | 264 | Using where | | 1 | SIMPLE | tbl_product | eq_ref | PRIMARY | PRIMARY | 4 | nnpro_mwrccars_migr.tbl_stock_item.product_id | 1 | Using where | | 1 | SIMPLE | tbl_config_manufacturer | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | | 1 | SIMPLE | tbl_waiting_for_delivery_qty | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 1 | SIMPLE | tbl_manual_supply_needs | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 1 | SIMPLE | tbl_manufacturer | eq_ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,nnpro_mwrccars_migr.tbl_config_manufacturer.value,const | 1 | Using where | | 1 | SIMPLE | tbl_name | eq_ref | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID | 8 | nnpro_mwrccars_migr.tbl_stock_item.product_id,const,const | 1 | | | 1 | SIMPLE | tbl_config_ideal | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | | 1 | SIMPLE | tbl_config_notify | ALL | NULL | NULL | NULL | NULL | 2909 | Using where | +------+-------------+------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+-------------------------------------------------------------------------------------------------------+------+-------------+