Description:
Hi!
I encounter a performance issue with MySQL on a specific query, where the optimizer is choosing ALL + BNL optimisation over a REF optimisation in query plan. This doesn't occur with MariaDB.
It has a big performance impact on "real" queries (150s -> 500ms for the original query because of this kind of issue!)
How to repeat:
DROP TABLE IF EXISTS `ps_product`;
CREATE TABLE `ps_product` (
`id_product` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id_product`)
) ENGINE=InnoDB AUTO_INCREMENT=195 DEFAULT CHARSET=latin1;
INSERT INTO `ps_product` VALUES (1),(8),(16),(19),(20),(24),(25),(35),(45),(87),(89),(122),(123),(124),(170),(171),(173),(179),(180),(187),(191),(194);
DROP TABLE IF EXISTS `ps_product_attribute_shop`;
CREATE TABLE `ps_product_attribute_shop` (
`id_product_attribute` int(10) unsigned NOT NULL,
`id_shop` int(10) unsigned NOT NULL,
`default_on` tinyint(1) unsigned DEFAULT NULL,
`id_product` int(10) unsigned NOT NULL,
`minimal_quantity` int(10) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id_product_attribute`,`id_shop`),
UNIQUE KEY `id_product` (`id_product`,`id_shop`,`default_on`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `ps_product_attribute_shop` VALUES (7,35,0,0,0),(7,36,0,0,0),(7,37,0,0,0),(7,38,0,0,0),(7,39,0,0,0);
EXPLAIN SELECT SQL_NO_CACHE p.*, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity FROM ps_product p LEFT JOIN `ps_product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1) WHERE p.`id_product` IN (1,2,3,4,5,6,7,8,9,10)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 22
filtered: 45.45
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: product_attribute_shop
partitions: NULL
type: ALL
possible_keys: id_product
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
=> ALL used instead of id_product
If I add an index on default_on:
ALTER TABLE ps_product_attribute_shop ADD KEY (default_on);
EXPLAIN SELECT SQL_NO_CACHE p.*, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity FROM ps_product p LEFT JOIN `ps_product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1) WHERE p.`id_product` IN (1,2,3,4,5,6,7,8,9,10)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 22
filtered: 45.45
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: product_attribute_shop
partitions: NULL
type: ref
possible_keys: id_product,default_on
key: id_product
key_len: 10
ref: test.p.id_product,const,const
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
=> adding an index on default_on make it uses properly id_product index?!
(the same occurs if we run an ANALYZE query on both tables);
Suggested fix:
Do not use ALL query_plan on a join if a perfect index is present!
Thanks!
Jocelyn Fournier
Description: Hi! I encounter a performance issue with MySQL on a specific query, where the optimizer is choosing ALL + BNL optimisation over a REF optimisation in query plan. This doesn't occur with MariaDB. It has a big performance impact on "real" queries (150s -> 500ms for the original query because of this kind of issue!) How to repeat: DROP TABLE IF EXISTS `ps_product`; CREATE TABLE `ps_product` ( `id_product` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id_product`) ) ENGINE=InnoDB AUTO_INCREMENT=195 DEFAULT CHARSET=latin1; INSERT INTO `ps_product` VALUES (1),(8),(16),(19),(20),(24),(25),(35),(45),(87),(89),(122),(123),(124),(170),(171),(173),(179),(180),(187),(191),(194); DROP TABLE IF EXISTS `ps_product_attribute_shop`; CREATE TABLE `ps_product_attribute_shop` ( `id_product_attribute` int(10) unsigned NOT NULL, `id_shop` int(10) unsigned NOT NULL, `default_on` tinyint(1) unsigned DEFAULT NULL, `id_product` int(10) unsigned NOT NULL, `minimal_quantity` int(10) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`id_product_attribute`,`id_shop`), UNIQUE KEY `id_product` (`id_product`,`id_shop`,`default_on`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `ps_product_attribute_shop` VALUES (7,35,0,0,0),(7,36,0,0,0),(7,37,0,0,0),(7,38,0,0,0),(7,39,0,0,0); EXPLAIN SELECT SQL_NO_CACHE p.*, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity FROM ps_product p LEFT JOIN `ps_product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1) WHERE p.`id_product` IN (1,2,3,4,5,6,7,8,9,10)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 22 filtered: 45.45 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: product_attribute_shop partitions: NULL type: ALL possible_keys: id_product key: NULL key_len: NULL ref: NULL rows: 5 filtered: 100.00 Extra: Using where; Using join buffer (Block Nested Loop) 2 rows in set, 1 warning (0.00 sec) => ALL used instead of id_product If I add an index on default_on: ALTER TABLE ps_product_attribute_shop ADD KEY (default_on); EXPLAIN SELECT SQL_NO_CACHE p.*, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity FROM ps_product p LEFT JOIN `ps_product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1) WHERE p.`id_product` IN (1,2,3,4,5,6,7,8,9,10)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 22 filtered: 45.45 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: product_attribute_shop partitions: NULL type: ref possible_keys: id_product,default_on key: id_product key_len: 10 ref: test.p.id_product,const,const rows: 1 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec) => adding an index on default_on make it uses properly id_product index?! (the same occurs if we run an ANALYZE query on both tables); Suggested fix: Do not use ALL query_plan on a join if a perfect index is present! Thanks! Jocelyn Fournier