| 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>
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);