| Bug #10031 | View doesn't use index | ||
|---|---|---|---|
| Submitted: | 20 Apr 2005 14:11 | Modified: | 29 Jun 2005 20:24 |
| Reporter: | Georg Richter | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.5 | OS: | Any (all) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[28 Jun 2005 14:27]
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/26486
[29 Jun 2005 17:23]
Igor Babaev
ChangeSet
1.2016 05/06/28 07:27:00 igor@rurik.mysql.com +3 -0
range.result, range.test:
Added a test case for bug #10031.
opt_range.cc:
Fixed bug #10031: range condition was not used with
views. Range analyzer did not take into account that
view columns were always referred through Item_ref.
The fix will appear in 5.0.9.
[29 Jun 2005 20:24]
Mike Hillyer
Documented in 5.0.9 changelog.
Description: If a view only uses 2nd part of an index for underlying table, it's very slow, even if the first part of index is in where clause of the SELECT FROM VIEW. Output for explains: explain select a,b from t1 where a < 2 and b=3 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 4 Extra: Using where; Using index explain select a,b from v1 where a < 2 and b=3 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 12 Extra: Using where; Using index How to repeat: CREATE TABLE t1 (a int, b int, primary key(a,b)); INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3), (4,1),(4,2),(4,3); CREATE VIEW v1 as SELECT a,b FROM t1 WHERE b=3;