Bug #88671 ALL + BNL chosen over REF in query plan with a simple SELECT + JOIN
Submitted: 28 Nov 2017 0:16 Modified: 4 Dec 2017 15:40
Reporter: jocelyn fournier Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.7.19 OS:Any
Assigned to:

[28 Nov 2017 0:16] 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
[4 Dec 2017 15:40] Sinisa Milivojevic
Hi Jocelyn,

Thank you for your bug report. I repeated the behaviour on the latest 5.7.

Verified as reported.