Bug #37742 HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested column
Submitted: 30 Jun 2008 14:33 Modified: 29 Jan 2009 20:21
Reporter: Zardosht Kasheff (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.23-rc OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: HA_EXTRA_KEYREAD, prefix
Triage: Triaged: D3 (Medium)

[30 Jun 2008 14:33] Zardosht Kasheff
Description:
Take a table with the following schema:

CREATE TABLE `foo` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`c`(3)),
  KEY `b` (`b`)
)

Insert 10 elements into the table. The perform the following query:
select c from please where b > SOME_VALUE;

For storage engines that expose the flag HA_PRIMARY_KEY_IN_READ_INDEX, MySQL will claim that this query is a covering index on the key b. The problem is that the primary key does not have the entire column of 'c', it has a prefix. Unless I misunderstand the meaning of the flag, the HA_EXTRA_KEYREAD flag should not be set here, because more than the key needs to be copied from the storage engine.

I will note that this scenario does not cause a behavioral bug in InnoDB. InnoDB seems to know about this problem and works around it. Nevertheless, it seems to be a bug.

How to repeat:
mentioned in the description

Suggested fix:
In such scenarios, the flag HA_EXTRA_KEYREAD should not be set
[1 Jul 2008 9:24] Susanne Ebrecht
Which storage engine exactly do you used?
[2 Jul 2008 12:19] Susanne Ebrecht
Verified as described by using 5.1 bzr tree.
CREATE TABLE `foo` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` char(10) NOT NULL DEFAULT '',
 PRIMARY KEY (`c`(3)),
KEY `b` (`b`)
)engine=innodb;

CREATE TABLE `bar` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` char(10) NOT NULL DEFAULT '',
 PRIMARY KEY (`c`(3)),
KEY `b` (`b`)
)engine=myisam;

insert into foo values(1,2,'abcdefghij');
insert into bar values(1,2,'abcdefghij');
insert into foo values(2,3,'');
insert into bar values(2,3,'');
insert into foo values(3,4,'klmnopqrst');
insert into bar values(3,4,'klmnopqrst');
insert into foo values(4,5,'uvwxyz');
insert into bar values(4,5,'uvwxyz');
insert into foo values(5,6,'meotnsyglt');
insert into bar values(5,6,'meotnsyglt');
insert into foo values(4,5,'asfdewe');
insert into bar values(4,5,'asfdewe');

INNODB:

explain select c from foo where b>2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: b
          key: b
      key_len: 5
          ref: NULL
         rows: 3
        Extra: Using where; Using index

MYISAM:
explain select c from bar where b>2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bar
         type: ALL
possible_keys: b
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
[2 Jul 2008 13:14] Susanne Ebrecht
I recognised that I did the wrong test.
[2 Jul 2008 13:19] Zardosht Kasheff
Actually, the test you describe above demonstrates the problem. The InnoDB query plan shows the issue, that MySQL is reporting this as a covering index when it is not.
[11 Nov 2008 10:45] 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/58436

2703 Georgi Kodinov	2008-11-11
      Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested
      column
      
      When the storage engine uses secondary keys clustered with the primary key MySQL was
      adding the primary key parts to each secondary key.
      In doing so it was not checking whether the index was on full columns and this
      resulted in the secondary keys being added to the list of covering keys even if 
      they have partial columns.
      Fixed by cleaning up the list of covering keys if the primary key has a partial
      column as a key part.
[29 Nov 2008 13:36] 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/60242

2703 Georgi Kodinov	2008-11-29
      Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested
      column
            
      When the storage engine uses secondary keys clustered with the primary key MySQL was
      adding the primary key parts to each secondary key.
      In doing so it was not checking whether the index was on full columns and this
      resulted in the secondary keys being added to the list of covering keys even if 
      they have partial columns.
      Fixed by not adding a primary key part to the list of columns that can be used 
      for index read of the secondary keys when the primary key part is a partial key part.
[15 Jan 2009 6:41] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[19 Jan 2009 11:24] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:02] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:07] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:55] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[29 Jan 2009 20:21] Paul Dubois
Noted in 5.1.31, 6.0.10 changelog.

Primary keys were treated as part of a covering index even if only a
prefix of a key column was used.