Bug #13317 VIEW doesn't use index

Submitted: 19 Sep 2005 10:11 Modified: 28 Sep 2005 1:59
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.14 OS:Linux (SLES 9)
Assigned to: Sergey Petrunya CPU Architecture:Any

[19 Sep 2005 10:11] Georg Richter
Description:
A view based on a single table uses an index scan, while select on the same table uses a range index.

Explain output:
root@georg>explain select * from t1 where a in (3,4)  and b in (1,2,3) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 6
        Extra: Using where; Using index
1 row in set (0.00 sec)

root@georg>explain select * from v1 where a in (3,4)  and b in (1,2,3) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 20
        Extra: Using where; Using index
1 row in set (0.00 sec)

How to repeat:
create table t1 (a int, b int, primary key(a,b));
create view v1 as select a, b from t1;

INSERT INTO `t1` VALUES (0,0),(1,0),(2,0),(3,0),(4,0),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(11,2),(12,2),(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3);

explain select * from t1 where a in (3,4)  and b in (1,2,3);
explain select * from v1 where a in (3,4)  and b in (1,2,3);
[20 Sep 2005 22:04] 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/30124
[21 Sep 2005 17:33] 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/30165
[24 Sep 2005 23:14] Sergey Petrunya
Fix pushed into 5.0.14  tree.
Bug description for the changelog:
  In queries over VIEWs that are processed using MERGE algorithm, range optimizer was unable to create ranges for "view.col IN (<constants>)" or "view.col BETWEEN const1 AND const2". This limitation is now removed.
[28 Sep 2005 1:59] Mike Hillyer
Added to 5.0.14 functionality change list:

<listitem>
        <para>
          Range scans can now be performed for queries on VIEWs such as
          <literal>column IN (&lt;constants&gt;)</literal> and <literal>column
            BETWEEN ConstantA AND ConstantB</literal>. (Bug #13317)
        </para>
      </listitem>

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.