| Bug #119713 | MySQL has hard time using the right indexes | ||
|---|---|---|---|
| Submitted: | 18 Jan 11:19 | Modified: | 20 Jan 9:35 |
| Reporter: | Aaditya Dubey | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.39, 8.0.44, 8.4.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Jan 12:18]
Aaditya Dubey
mysql [localhost:8407] {root} (test) > explain SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`,
-> pl.`name`, image_shop.`id_image` id_image, il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB("2024-10-09 00:00:00", INTERVAL 30 DAY)) > 0 AS new, product_shop.price AS orderprice FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 7) LEFT JOIN ps_stock_available stock ON (stock.id_product = `p`.id_product
-> AND stock.id_product_attribute = 0 AND stock.id_shop_group = 2 AND stock.id_shop = 0 ) LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 4 AND cl.id_shop = 7 ) LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 4 AND pl.id_shop = 7 ) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=7) LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 4) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = 7 AND cp.`id_category` = 115 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY cp.`position` ASC LIMIT 0,35;
+----+-------------+--------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------+---------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------+---------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | cp | NULL | index | ps_category_product_id_product_id_category_u,ps_category_product_id_product,ps_product_idx_id_product_id_categor_position | ps_category_product_position | 4 | NULL | 3386491 | 10.00 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | product_shop | NULL | eq_ref | PRIMARY,ps_shop_idx_id_shop_active_visibility | PRIMARY | 8 | test.cp.id_product,const | 1 | 5.00 | Using where |
| 1 | SIMPLE | p | NULL | eq_ref | PRIMARY,ps_product_idx_id_product_id_manufacturer | PRIMARY | 4 | test.cp.id_product | 1 | 100.00 | NULL |
| 1 | SIMPLE | stock | NULL | eq_ref | product_sqlstock,id_shop,id_shop_group,id_product,id_product_attribute | product_sqlstock | 16 | test.cp.id_product,const,const,const | 1 | 100.00 | NULL |
| 1 | SIMPLE | cl | NULL | eq_ref | PRIMARY | PRIMARY | 12 | test.product_shop.id_category_default,const,const | 1 | 100.00 | NULL |
| 1 | SIMPLE | pl | NULL | eq_ref | PRIMARY,id_lang | PRIMARY | 12 | test.cp.id_product,const,const | 1 | 100.00 | NULL |
| 1 | SIMPLE | image_shop | NULL | eq_ref | id_product,id_shop | id_product | 10 | test.cp.id_product,const,const | 1 | 100.00 | Using index |
| 1 | SIMPLE | il | NULL | eq_ref | PRIMARY,id_image | PRIMARY | 8 | test.image_shop.id_image,const | 1 | 100.00 | NULL |
| 1 | SIMPLE | m | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+--------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+---------------------------------------------------+---------+----------+-----------------------------------------------------------+
9 rows in set, 1 warning (0.02 sec)
[20 Jan 9:35]
Øystein Grøvlen
Hi Aaditya, Thank you, for your bug report. The main query is verified as described. A work-around is to set optimizer_switch='block_nested_loop=off' since the use of hash join disrupts the natural sort order and requires the entire query result to be sorted. For the other queries you mention, I fail to see that there are any better query plans. The use of LEFT JOIN, limits possible join orders. Do you have any indication that better query plans exist?
Description: In MariaDB, the page loaded in ~0.5 seconds. In MySQL, the same query runs much slower, increasing the page load time from ~0.5 seconds to 3–5+ seconds. It looks like a few queries that “fly” on MariaDB don’t perform nearly as well on MySQL. What’s worse: when we upgraded from MySQL 8.0 to 8.4, things got even slower by about 0.2–0.3 seconds instead of improving. How to repeat: Load the data: prod_db.sql.gz Run the below-mentioned queries on 8.0 and 8.4 MySQL 8.0.44: 1st iteration: SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image, il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB("2024-10-09 00:00:00", INTERVAL 30 DAY)) > 0 AS new, product_shop.price AS orderprice FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 7) LEFT JOIN ps_stock_available stock ON (stock.id_product = `p`.id_product AND stock.id_product_attribute = 0 AND stock.id_shop_group = 2 AND stock.id_shop = 0 ) LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 4 AND cl.id_shop = 7 ) LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 4 AND pl.id_shop = 7 ) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=7) LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 4) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = 7 AND cp.`id_category` = 115 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY cp.`position` ASC LIMIT 0,35; ... 35 rows in set, 65535 warnings (18.11 sec) 2nd iteration: mysql [localhost:8047] {root} (test) > SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image, il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB("2024-10-09 00:00:00", INTERVAL 30 DAY)) > 0 AS new, product_shop.price AS orderprice FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 7) LEFT JOIN ps_stock_available stock ON (stock.id_product = `p`.id_product AND stock.id_product_attribute = 0 AND stock.id_shop_group = 2 AND stock.id_shop = 0 ) LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 4 AND cl.id_shop = 7 ) LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 4 AND pl.id_shop = 7 ) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=7) LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 4) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = 7 AND cp.`id_category` = 115 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY cp.`position` ASC LIMIT 0,35; 35 rows in set, 65535 warnings (3.45 sec) MySQL 8.4.6: 1st iteration: mysql [localhost:8407] {root} (test) > SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image, il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB("2024-10-09 00:00:00", INTERVAL 30 DAY)) > 0 AS new, product_shop.price AS orderprice FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 7) LEFT JOIN ps_stock_available stock ON (stock.id_product = `p`.id_product AND stock.id_product_attribute = 0 AND stock.id_shop_group = 2 AND stock.id_shop = 0 ) LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 4 AND cl.id_shop = 7 ) LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 4 AND pl.id_shop = 7 ) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=7) LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 4) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = 7 AND cp.`id_category` = 115 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY cp.`position` ASC LIMIT 0,35; 35 rows in set, 65535 warnings (30.35 sec) 2nd iteration: mysql [localhost:8407] {root} (test) > SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image, il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB("2024-10-09 00:00:00", INTERVAL 30 DAY)) > 0 AS new, product_shop.price AS orderprice FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 7) LEFT JOIN ps_stock_available stock ON (stock.id_product = `p`.id_product AND stock.id_product_attribute = 0 AND stock.id_shop_group = 2 AND stock.id_shop = 0 ) LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 4 AND cl.id_shop = 7 ) LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 4 AND pl.id_shop = 7 ) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=7) LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 4) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = 7 AND cp.`id_category` = 115 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY cp.`position` ASC LIMIT 0,35; 35 rows in set, 65535 warnings (28.68 sec) We have some more queries with a similar performance issue: mysql [localhost:8407] {root} (test) > SELECT COUNT(DISTINCT p.id_product) FROM ps_product p LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = '4' AND pl.id_shop = p.id_shop_default LEFT JOIN ps_product_shop ps ON p.id_product = ps.id_product AND ps.id_shop = p.id_shop_default WHERE NOT EXISTS( SELECT 1 FROM ps_image_shop img WHERE (p.id_product = img.id_product) AND (img.id_shop = p.id_shop_default) ); 1 row in set (27.05 sec) mysql [localhost:8407] {root} (test) > SELECT p.id_product, p.reference, p.active, pl.name FROM ps_product p LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = '4' AND pl.id_shop = p.id_shop_default LEFT JOIN ps_product_shop ps ON p.id_product = ps.id_product AND ps.id_shop = p.id_shop_default WHERE EXISTS( SELECT 1 FROM ps_stock_available stock WHERE (p.id_product = stock.id_product) AND ( NOT EXISTS( SELECT 1 FROM ps_product_attribute pa WHERE pa.id_product = p.id_product ) ) AND (IFNULL(stock.quantity, 0) <= 0) ) ORDER BY name asc LIMIT 20; 20 rows in set (1 min 7.06 sec) mysql [localhost:8407] {root} (test) > SELECT COUNT(DISTINCT p.id_product) FROM ps_product p LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = '4' AND pl.id_shop = p.id_shop_default LEFT JOIN ps_product_shop ps ON p.id_product = ps.id_product AND ps.id_shop = p.id_shop_default WHERE (p.price = 0) AND (p.wholesale_price = 0) AND ( NOT EXISTS( SELECT 1 FROM ps_specific_price sp WHERE p.id_product = sp.id_product ) ); 1 row in set (20.11 sec)