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: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.14 | OS: | Linux (SLES 9) |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[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 (<constants>)</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.
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);