Bug #13327 VIEW performs index scan
Submitted: 19 Sep 2005 16:29 Modified: 13 Oct 2005 2:38
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.14 OS:
Assigned to: Evgeny Potemkin

[19 Sep 2005 16:29] Georg Richter
Description:
View with a simple join on 2 fields performs an index scan, while corresponding select statement uses an index.

Explain output:

mysql> explain select * from v1 where a=1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t2
         type: index
possible_keys: a
          key: a
      key_len: 10
          ref: NULL
         rows: 20
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: ref
possible_keys: a
          key: a
      key_len: 10
          ref: const,test.t2.b
         rows: 2
        Extra: Using where; Using index
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT t1.* FROM t1 join t2 where t1.a=t2.a and t1.b=t2.b and t1.a=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: a
          key: a
      key_len: 5
          ref: const
         rows: 10
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: a
          key: a
      key_len: 10
          ref: const,test.t2.b
         rows: 2
        Extra: Using where; Using index
2 rows in set (0.00 sec)

How to repeat:
CREATE TABLE t1 (a int, b int, index(a,b));
CREATE TABLE t2 LIKE t1;

INSERT INTO `t1` VALUES (1,1),(1,5),(1,5),(1,5),(1,7),(1,7),(1,8),(1,9),(1,10),(1,10),(1,10),(1,13),(1,13),(1,14),(1,15),(1,16),(1,16),(1,17),(1,17),(1,18),(1,19),(1,19),(1,19),(1,20),(1,20),(1,21),(1,22),(1,22),(1,22),(1,23),(1,23),(1,23),(1,25),(1,25),(1,25),(1,25),(1,26),(1,26),(1,27),(1,27),(1,27),(1,27),(1,28),(1,29),(1,31),(1,33),(1,33),(1,34),(1,34),(1,35),(1,35),(1,37),(1,37),(1,38),(1,38),(1,39),(1,39),(1,40),(1,40),(1,40),(1,41),(1,42),(1,42),(1,42),(1,42),(1,45),(1,45),(1,45),(1,46),(1,47),(1,47),(1,50),(1,51),(1,52),(1,52),(1,54),(1,54),(1,54),(1,55),(1,55),(1,56),(1,56),(1,56),(1,57),(1,58),(1,58),(1,58),(1,59),(1,60),(1,62),(1,62),(1,63),(1,63),(1,63),(1,64),(1,64),(1,66),(1,67),(1,68),(1,71),(1,72),(1,74),(1,75),(1,75),(1,77),(1,79),(1,81),(1,83),(1,84),(1,87),(1,87),(1,87),(1,87),(1,87),(1,87),(1,87),(1,91),(1,93),(1,93),(1,93),(1,95),(1,95),(1,96),(1,96),(1,97),(1,97),(1,97),(1,97);

INSERT INTO `t2` VALUES (1,1),(1,1),(1,5),(1,5),(1,5),(1,7),(1,7),(1,8),(1,9),(1,10),(1,10),(5,1),(5,1),(5,1),(7,1),(7,1),(8,1),(9,1),(10,1),(10,1);

CREATE VIEW v1 AS SELECT t1.* FROM t1 join t2 where t1.a=t2.a and t1.b=t2.b;

explain select * from v1 where a=1;
EXPLAIN SELECT t1.* FROM t1 join t2 where t1.a=t2.a and t1.b=t2.b and t1.a=1;
[3 Oct 2005 14:23] 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/internals/30638
[10 Oct 2005 14:53] 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/internals/30860
[11 Oct 2005 20:14] Evgeny Potemkin
check_equality() finds equalities among field items. It checks input items
to be Item_fields thus skipping view's fields, which are represented by
Item_direct_view_ref. Because of this index wasn't applied in all cases
it can be.

Fixed in 5.0.15, cset 1.2019.3.9
[13 Oct 2005 2:38] Mike Hillyer
Documented in 5.0.15 changelog:

<listitem>
        <para>
          Queries that use index in normal SELECT may cause range scan
          in VIEWs. (Bug #13327)
        </para>
      </listitem>