Bug #34078 ref access is not used for varchar column
Submitted: 26 Jan 2008 14:19 Modified: 22 Feb 2008 14:16
Reporter: Sveta Smirnova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1,5.0,5.1,6.0-BK OS:Any (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[26 Jan 2008 14:19] Sveta Smirnova 
Description:
'not exists' optimization for outer joins and VARCHAR column doesn't work:

mysql> show create table imagelinks;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                        |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| imagelinks | CREATE TABLE `imagelinks` (
  `il_from` int(10) unsigned NOT NULL default '0',
  `il_to` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
  `img_id` int(11) default NULL,
  UNIQUE KEY `il_from` (`il_from`,`il_to`),
  KEY `il_to` (`il_to`,`il_from`),
  KEY `img_id` (`img_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> show create table page_associate_image;;
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                | Create Table                                                                                                                                                                                                                                                                                                                     |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| page_associate_image | CREATE TABLE `page_associate_image` (
  `page_id` int(8) NOT NULL,
  `img_name` varchar(255) NOT NULL,
  `img_id` int(11) default NULL,
  KEY `page_id` (`page_id`),
  KEY `IDX_page_associate_image_img_name` (`img_name`),
  KEY `img_name` (`img_name`(10)),
  KEY `img_id` (`img_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from page_associate_image;
+------------+----------+--------+
| page_id    | img_name | img_id |
+------------+----------+--------+
| -634462102 | iii      |      1 | 
| -597187184 | iii      |      1 | 
| -821211600 | iii      |      1 | 
|          0 | iii      |      1 | 
| -566146336 | iii      |      1 | 
+------------+----------+--------+
5 rows in set (0.09 sec)

mysql> select * from imagelinks;
+------------+-------+--------+
| il_from    | il_to | img_id |
+------------+-------+--------+
| 2056786463 | iii   |      1 | 
| 2066518885 | iii   |      1 | 
| 2066783021 | iii   |      1 | 
| 2091789635 | iii   |      1 | 
| 2102645533 | iii   |      1 | 
+------------+-------+--------+
5 rows in set (0.00 sec)

mysql> EXPLAIN SELECT i.il_from, i.il_to FROM imagelinks i LEFT JOIN page_associate_image pai ON i.il_to = pai.img_name WHERE pai.img_name IS NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: index
possible_keys: NULL
          key: img_id
      key_len: 5
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pai
         type: index
possible_keys: IDX_page_associate_image_img_name,img_name
          key: IDX_page_associate_image_img_name
      key_len: 257
          ref: NULL
         rows: 5
        Extra: Using where; Using index; Not exists
2 rows in set (0.00 sec)

But with INTEGER columns optimization works:

mysql> EXPLAIN SELECT i.il_from, i.il_to FROM imagelinks i LEFT JOIN page_associate_image pai ON i.img_id = pai.img_id WHERE pai.img_name IS NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: index
possible_keys: NULL
          key: img_id
      key_len: 5
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pai
         type: ref
possible_keys: img_id
          key: img_id
      key_len: 5
          ref: issue22635.i.img_id
         rows: 2
        Extra: Using where; Not exists
2 rows in set (0.01 sec)

How to repeat:
--source include/have_innodb.inc

--disable_result_log
--disable_query_log

DROP TABLE IF EXISTS `page_associate_image`;
CREATE TABLE `page_associate_image` (
  `page_id` int(8) NOT NULL,
  `img_name` varchar(255) NOT NULL,
  `img_id` int(11) default NULL,
  KEY `page_id` (`page_id`),
  KEY `IDX_page_associate_image_img_name` (`img_name`),
  KEY `img_name` (`img_name`(10)),
  KEY `img_id` (`img_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `page_associate_image` VALUES (-634462102,'iii',1),(-597187184,'iii',1),(-821211600,'iii',1),(0,'iii',1),(-566146336,'iii',1);

DROP TABLE IF EXISTS `imagelinks`;
CREATE TABLE `imagelinks` (
  `il_from` int(10) unsigned NOT NULL default '0',
  `il_to` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
  `img_id` int(11) default NULL,
  UNIQUE KEY `il_from` (`il_from`,`il_to`),
  KEY `il_to` (`il_to`,`il_from`),
  KEY `img_id` (`img_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `imagelinks` VALUES (2056786463,'iii',1),(2066518885,'iii',1),(2066783021,'iii',1),(2091789635,'iii',1),(2102645533,'iii',1);

--enable_query_log
--enable_result_log

EXPLAIN SELECT i.il_from, i.il_to
FROM imagelinks i
LEFT JOIN page_associate_image pai
ON i.il_to = pai.img_name
WHERE pai.img_name IS NULL;

EXPLAIN SELECT i.il_from, i.il_to
FROM imagelinks i
LEFT JOIN page_associate_image pai
ON i.img_id = pai.img_id
WHERE pai.img_name IS NULL;

Suggested fix:
Don't scan all rows to output 0 rows
[26 Jan 2008 14:21] Sveta Smirnova 
Workaround: add column with integer ID as I did in my second example to get better optimisation.
[27 Jan 2008 1:16] Sergey Petrunya 
Root of the problem: the fist query uses ref access, the second doesn't.

mysql>  EXPLAIN SELECT i.il_from, i.il_to FROM imagelinks i JOIN page_associate_image pai force index (img_id) ON i.img_id = pai.img_id WHERE 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: index
possible_keys: img_id
          key: img_id
      key_len: 5
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pai
         type: ref
possible_keys: img_id
          key: img_id
      key_len: 5
          ref: j12.i.img_id
         rows: 2
        Extra: Using where; Using index
2 rows in set (0.01 sec)

mysql>  EXPLAIN SELECT i.il_from, i.il_to  FROM imagelinks i  JOIN page_associate_image pai force index(IDX_page_associate_image_img_name,img_name) ON i.il_to = pai.img_name  WHERE 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: index
possible_keys: il_to
          key: img_id
      key_len: 5
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pai
         type: index
possible_keys: IDX_page_associate_image_img_name,img_name
          key: IDX_page_associate_image_img_name
      key_len: 257
          ref: NULL
         rows: 5
        Extra: Using where; Using index
2 rows in set (0.01 sec)
[27 Jan 2008 1:24] Sergey Petrunya 
As it is shown in previous comment, the problem is not related to outer joins or "Not Exists" optimization. 

Changing bug synopsis accordingly.
[22 Feb 2008 14:12] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42829

ChangeSet@1.2189, 2008-02-22 15:12:50+01:00, jonas@perch.ndb.mysql.com +1 -0
  bug#34078 backport to drop6 (ndb)
[22 Feb 2008 14:16] Jonas Oreland 
sorry, committed with incorrect bug no
[22 Feb 2008 14:18] Jonas Oreland 
restore state...