Description:
When a join buffer is employed to join a table and there is a covering index for this table then the optimizer usually chooses the index scan to get the needed fields of the joined table. The optimizer choice can be see from the output of the corresponding EXPLAIN. However the executor still uses a table scan to fetch these fields. This can be seen from the output of an appropriate SHOW STATUS command.
How to repeat:
Create the following tables:
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b));
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
Populate them like this:
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3);
INSERT INTO t2 VALUES
(1,1,2), (3,1,3), (1,2,2), (4,4,2),(1,1,1), (3,1,1), (1,2,1);
Run the following sequence of the commands:
mysql> EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+---------------------------------------------+
| 1 | SIMPLE | t1 | ref | PRIMARY | PRIMARY | 4 | const | 2 | Using index |
| 1 | SIMPLE | t2 | index | NULL | PRIMARY | 12 | NULL | 7 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+---------------------------------------------+
2 rows in set (0.00 sec)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b;
+---+---+---+---+---+
| a | b | a | b | c |
+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 2 |
| 1 | 1 | 3 | 1 | 3 |
| 1 | 2 | 1 | 2 | 2 |
| 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 3 | 1 | 1 |
| 1 | 2 | 1 | 2 | 1 |
+---+---+---+---+---+
6 rows in set (0.08 sec)
mysql> SHOW STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 2 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 8 |
+-----------------------+-------+
6 rows in set (0.13 sec)
I checked the problem for the current tree of version 6.0, but most probably the it exists for the previous versions as well.
