Bug #5333 "const" access type does not print "using index" or does not use index only

• Submitted: 1 Sep 2004 6:28
• Modified: 8 Sep 2004 4:15
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.4
• OS: Any (all)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[1 Sep 2004 6:28] Peter Zaitsev

Description:
As you can see below if primary key is used for "const" access we have "using index" 
specified  while in case of secondary unique index we do not get "using index" for
"const" access, however if we get  "range" access instead we'll get "using index" back again.

I do not know if it is explain bug or data file is really being read in this case. 

mysql> explain select c from t1 where c="a";
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | const | c             | c    |      10 | const |    1 |       |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select i from t1 where i=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY |       4 | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

How to repeat:

CREATE TABLE t1 (
  i int(11) NOT NULL default '0',
  c char(10) NOT NULL default '',
  PRIMARY KEY  (i),
  UNIQUE KEY c (c)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t1`
--

INSERT INTO t1 VALUES (1,'a');
INSERT INTO t1 VALUES (2,'b');
INSERT INTO t1 VALUES (3,'c');

[3 Sep 2004 8:46] Igor Babaev

The bug is due to the fact that there is no setting for field index of the JOIN_TAB structure for const tables in the function join_read_const_table. As a result the value of this field is always 0. Nevertheless the function select_describe is trying to use the value of the field when checking whether the used index is covering or not.
So the presence of note 'Using index' in the plan depends only on the ordinal number of the used index: only if it's the first index the note is there.

In 4.0 both plans of the test case do not contain that note. It is also a bug.

First I downported some code from 4.1 to to get the same result as in the test case. Then I added the statement tab->index= tab->ref.key after the statement
table->file->extra(HA_EXTRA_KEYREAD) in join_read_const_table.

I also added the test case to select.test.
See ChangeSet:
 1.1979 04/09/02 22:06:30 [e-mail] +5 -0
for 4.0