Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.17, 10.5.18
-
None
-
None
-
None
Description
We are currently evaluating the issue so some info may be missing .
CREATE TABLE `ps_stock_available` (
|
`id_stock_available` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`id_product` int(11) unsigned NOT NULL,
|
`id_product_attribute` int(11) unsigned NOT NULL,
|
`id_shop` int(11) unsigned NOT NULL,
|
`id_shop_group` int(11) unsigned NOT NULL,
|
`quantity` int(10) NOT NULL DEFAULT 0,
|
`physical_quantity` int(11) NOT NULL DEFAULT 0,
|
`reserved_quantity` int(11) NOT NULL DEFAULT 0,
|
`depends_on_stock` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`out_of_stock` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`location` varchar(255) NOT NULL DEFAULT '',
|
PRIMARY KEY (`id_stock_available`),
|
UNIQUE KEY `product_sqlstock` (`id_product`,`id_product_attribute`,`id_shop`,`id_shop_group`),
|
KEY `id_shop` (`id_shop`),
|
KEY `id_shop_group` (`id_shop_group`),
|
KEY `id_product` (`id_product`),
|
KEY `id_product_attribute` (`id_product_attribute`),
|
KEY `id_product_id_shop` (`id_product`,`id_shop`)
|
) ENGINE=InnoDB AUTO_INCREMENT=25497 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED
|
CREATE TABLE `ps_order_detail` (
|
`id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`id_order` int(10) unsigned NOT NULL,
|
`id_order_invoice` int(11) DEFAULT NULL,
|
`id_warehouse` int(10) unsigned DEFAULT 0,
|
`id_shop` int(11) unsigned NOT NULL,
|
`product_id` int(10) unsigned NOT NULL,
|
`product_attribute_id` int(10) unsigned DEFAULT NULL,
|
`id_customization` int(10) DEFAULT 0,
|
`product_name` varchar(255) NOT NULL,
|
`product_quantity` int(10) unsigned NOT NULL DEFAULT 0,
|
`product_quantity_in_stock` int(10) NOT NULL DEFAULT 0,
|
`product_quantity_refunded` int(10) unsigned NOT NULL DEFAULT 0,
|
`product_quantity_return` int(10) unsigned NOT NULL DEFAULT 0,
|
`product_quantity_reinjected` int(10) unsigned NOT NULL DEFAULT 0,
|
`product_price` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`reduction_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
|
`reduction_amount` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`reduction_amount_tax_incl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`reduction_amount_tax_excl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`group_reduction` decimal(5,2) NOT NULL DEFAULT 0.00,
|
`product_quantity_discount` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`product_ean13` varchar(13) DEFAULT NULL,
|
`product_upc` varchar(12) DEFAULT NULL,
|
`product_mpn` varchar(40) DEFAULT NULL,
|
`product_reference` varchar(64) DEFAULT NULL,
|
`product_supplier_reference` varchar(64) DEFAULT NULL,
|
`product_weight` decimal(20,6) NOT NULL,
|
`id_tax_rules_group` int(11) unsigned DEFAULT 0,
|
`tax_computation_method` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`tax_name` varchar(16) NOT NULL,
|
`tax_rate` decimal(10,3) NOT NULL DEFAULT 0.000,
|
`ecotax` decimal(17,6) NOT NULL DEFAULT 0.000000,
|
`ecotax_tax_rate` decimal(5,3) NOT NULL DEFAULT 0.000,
|
`discount_quantity_applied` tinyint(1) NOT NULL DEFAULT 0,
|
`download_hash` varchar(255) DEFAULT NULL,
|
`download_nb` int(10) unsigned DEFAULT 0,
|
`download_deadline` datetime DEFAULT NULL,
|
`total_price_tax_incl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`total_price_tax_excl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`unit_price_tax_incl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`unit_price_tax_excl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`total_shipping_price_tax_incl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`total_shipping_price_tax_excl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`purchase_supplier_price` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`original_product_price` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`original_wholesale_price` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`total_refunded_tax_excl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`total_refunded_tax_incl` decimal(20,6) NOT NULL DEFAULT 0.000000,
|
`sc_attr_infos_v1` text NOT NULL,
|
`product_isbn` varchar(32) DEFAULT NULL,
|
PRIMARY KEY (`id_order_detail`),
|
KEY `order_detail_order` (`id_order`),
|
KEY `product_attribute_id` (`product_attribute_id`),
|
KEY `id_order_id_order_detail` (`id_order`,`id_order_detail`),
|
KEY `id_tax_rules_group` (`id_tax_rules_group`),
|
KEY `id_order_detail` (`id_order_detail`),
|
KEY `product_id` (`product_id`,`product_attribute_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=1065515 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPRESSED
|
First we found a schema difference using int(10) vs int(11) on the concerned tables
MariaDB [fumeur3]> SELECT product_id FROM ps_order_detail WHERE id_order = 350730;
|
+------------+
|
| product_id |
|
+------------+
|
| 8479 |
|
| 9703 |
|
| 7174 |
|
| 2407 |
|
| 2412 |
|
+------------+
|
explain UPDATE ps_stock_available ... WHERE sa.id_shop = 1 AND sa.id_product IN (SELECT product_id FROM ps_order_detail WHERE id_order = 350730);
|
 |
+------+--------------------+-----------------+--------+-----------------------------------------------------------------------------+----------------------+---------+---------------------------------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-----------------+--------+-----------------------------------------------------------------------------+----------------------+---------+---------------------------------+-------+-------------+
|
| 1 | PRIMARY | sa | index | id_shop | PRIMARY | 4 | NULL | 21703 | Using where |
|
| 3 | DEPENDENT SUBQUERY | ps_order_detail | ref | order_detail_order,id_order_id_order_detail,product_id | order_detail_order | 4 | const | 5 | Using where |
|
| 2 | DEPENDENT SUBQUERY | od | ref | order_detail_order,product_attribute_id,id_order_id_order_detail,product_id | product_attribute_id | 5 | fumeur3.sa.id_product_attribute | 23 | Using where |
|
| 2 | DEPENDENT SUBQUERY | o | eq_ref | PRIMARY,id_shop,current_state | PRIMARY | 4 | fumeur3.od.id_order | 1 | Using where |
|
| 2 | DEPENDENT SUBQUERY | os | eq_ref | PRIMARY | PRIMARY | 4 | fumeur3.o.current_state | 1 | Using where |
|
+------+--------------------+-----------------+--------+-----------------------------------------------------------------------------+----------------------+---------+---------------------------------+-------+-------------+
|
The optimizer choice of full index scan is not very efficient and causing deadlock on concurrency by locking such big range.
The corresponding rewrite query in a select does a correct plan
explain Select sa.reserved_quantity FROM ps_stock_available sa WHERE sa.id_shop = 1
AND sa.id_product IN (SELECT product_id FROM ps_order_detail WHERE id_order = 350730);
+------+--------------+-----------------+------+----------------------------------------------------------------------------+---------------------+---------+-------------------------------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-----------------+------+----------------------------------------------------------------------------+---------------------+---------+-------------------------------------------+------+-------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 1 | |
|
| 1 | PRIMARY | sa | ref | product_sqlstock,id_shop,id_product,id_product_id_shop | id_product | 4 | preprod_fumeur.ps_order_detail.product_id | 1 | Using where |
|
| 2 | MATERIALIZED | ps_order_detail | ref | order_detail_order,id_order_id_order_detail,product_id,id_order_product_id | id_order_product_id | 4 | const | 1 | Using index |
|
+------+--------------+-----------------+------+----------------------------------------------------------------------------+---------------------+-
|
Now the best possible index to satisfy the condition WHERE sa.id_shop = 1 AND sa.id_product would be (id_shop,id_product) as it is translating to (CONST, SMALL RANGE)
But at least the index show as possible usage but still is not picked and probably because the feature is missing in this release can you confirm ?
To let the optimizer know about the cardinality of the small range without using EITS
We added the id_order, id_product
Disabling most optimizer switch does not look to have any effect on the plan .
Note replacing in subquery queries with constant take correct plan
Attaching optimizer trace of the original query