Bug #19195 Optimizer does not use proper index for join
Submitted: 19 Apr 2006 12:09 Modified: 25 Sep 2009 4:08
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.27-BK, 5.0.21-BK, 5.0.19 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[19 Apr 2006 12:09] Valeriy Kravchuk 
Description:
Optimizer uses complex index in a single-table query with constants:

mysql> explain select count(*) from item where productcode = 'ZQXW0' and customerspecific=0 and color='500' and dataformat='FP1';
+----+-------------+-------+------+--------------------------------------------+
----------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys                              |
 key      | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+--------------------------------------------+
----------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | item  | ref  | item_1,item_5,Item_color,complex1,complex2 |
 complex1 | 1537    | const,const,const |    1 | Using where |
+----+-------------+-------+------+--------------------------------------------+
----------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

But does not use the same index when 2 of those columns are joined to the other table:

mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
|      746 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from item, product
    -> where
    -> item.customerspecific = 0 and
    -> item.color = product.maincolor and
    -> item.dataformat = 'FP1' and
    -> item.productcode = product.code;
+----------+
| count(*) |
+----------+
|       61 |
+----------+
1 row in set (0.02 sec)

mysql> explain select count(*) from item, product where item.customerspecific = 0 and item.color = product.maincolor and item.dataformat = 'FP1' and item.productcode = product.code\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: item
         type: ALL
possible_keys: item_1,item_5,Item_color,complex1,complex2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3710
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: product
         type: eq_ref
possible_keys: PRIMARY,product_idx_004
          key: PRIMARY
      key_len: 752
          ref: cir.item.productcode
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

Even in case there is no proper indexes on product table, optimizer should read it all first (746 rows only) and then use complex1 index to find match in item table by consts, as in the first EXPLAIN.

How to repeat:
1. Create tables:

CREATE TABLE `item` (
  `code` varchar(300) NOT NULL default '',
  `productcode` varchar(300) NOT NULL default '',
  `seq` int(10) unsigned default NULL,
  `color` varchar(300) NOT NULL,
  `dataformat` varchar(10) default NULL,
  `noteseq` varchar(20) default NULL,
  `customerspecific` int(10) unsigned default NULL,
  `privatelabel` int(10) unsigned default NULL,
  `cartonpieces` varchar(100) default NULL,
  `cartonweight` varchar(100) default NULL,
  `cartonvolume` varchar(100) default NULL,
  `palletcarton` varchar(100) default NULL,
  `palletvolume` varchar(100) default NULL,
  `weight` varchar(100) default NULL,
  `length` varchar(100) default NULL,
  `cartonlength` varchar(100) default NULL,
  `salespriceunit` varchar(20) default NULL,
  PRIMARY KEY  (`code`(250),`productcode`(250)),
  KEY `item_1` (`productcode`(250),`code`(250)),
  KEY `item_5` (`productcode`(250),`privatelabel`,`color`(250)),
  KEY `item_dataformat` (`dataformat`),
  KEY `Item_customerspecific` (`customerspecific`),
  KEY `Item_color` (`color`(250)),
  KEY `complex1` (`productcode`(250),`color`(250),`dataformat`,`privatelabel`),
  KEY `complex2` (`productcode`(250),`color`(250),`dataformat`,`customerspecific`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `product` (
  `producttypecode` varchar(100) character set utf8 collate utf8_bin NOT NULL,
   `code` varchar(300) character set utf8 collate utf8_bin NOT NULL,
   `module` varchar(100) character set utf8 collate utf8_bin default NULL,
   `maincolor` varchar(100) character set utf8 collate utf8_bin default NULL,
   `p1` varchar(100) character set utf8 collate utf8_bin default NULL,
   `p2` varchar(100) character set utf8 collate utf8_bin default NULL,
   `p3` varchar(100) character set utf8 collate utf8_bin default NULL,
   `p4` varchar(100) character set utf8 collate utf8_bin default NULL,
   `slots` int(10) unsigned default NULL,
   `imperial` int(10) unsigned default NULL,
   `width` varchar(100) character set utf8 collate utf8_bin default NULL,
   `thickness` varchar(100) character set utf8 collate utf8_bin default NULL,
   `height` varchar(100) character set utf8 collate utf8_bin default NULL,
   `seq` int(10) unsigned default NULL,
   `maxload` varchar(50) character set utf8 collate utf8_bin default NULL,
   PRIMARY KEY  (`code`(250)),
   UNIQUE KEY `product_idx_004` (`code`(250),`producttypecode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Put some data according to the description.

3. Run EXPLAIN:

explain select count(*) from item, product where item.customerspecific = 0 and item.color = product.maincolor and item.dataformat = 'FP1' and item.productcode = product.code\G

Suggested fix:
Use multiple column indexes when they contains all the columns used in query, even with joins.
[16 Oct 2006 22:10] Igor Babaev 
It's absolutely unclear why the optimizer should select a plan different from that
he has chosen. 
I have to mark the case as 'Not a Bug'.
[29 Sep 2009 23:25] Omer Barnir 
triage: - based on Igor's comment [17 Oct 2006 0:10], this is at least E4/R4, setting to CHECKED bug