| Bug #10031 | View doesn't use index | ||
|---|---|---|---|
| Submitted: | 20 Apr 2005 16:11 | Modified: | 29 Jun 2005 22:24 |
| Reporter: | Georg Richter | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S1 (Critical) |
| Version: | 5.0.5 | OS: | Any (all) |
| Assigned to: | Igor Babaev | Target Version: | |
[28 Jun 2005 16: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 19: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 22: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;