| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.14 | OS: | |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[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>
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;