Re: use index on a view
Posted by:
Rick James
Date: February 11, 2009 09:17PM
I agree with your view of views. Here is a very simple test that seems to agree with your expectations:
Server version: 5.1.30-community MySQL Community Server (GPL)
mysql> create table view1 (i int);
mysql> create table view2 (i int);
mysql> insert into view1 values (1), (2);
mysql> insert into view2 values (3), (2);
mysql> create view viewn as select * from view1 union all select * from view2;
mysql> select * from viewn;
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 2 |
+------+
mysql> select * from viewn where i = 1;
+------+
| i |
+------+
| 1 |
+------+
mysql> select * from viewn where i = 2;
+------+
| i |
+------+
| 2 |
| 2 |
+------+
Now to add an index and do explain:
mysql> alter table view1 add index(i);
mysql> explain
-> select * from viewn where i = 2\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: view1
type: index
possible_keys: NULL
key: i
key_len: 5
ref: NULL
rows: 2
Extra: Using index
*************************** 3. row ***************************
id: 3
select_type: UNION
table: view2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*************************** 4. row ***************************
id: NULL
select_type: UNION RESULT
table: <union2,3>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
Note how it used the index that now exists on view1, but no index on the other. This implies that it "does the right thing"?? Not sure how to reconcile it with what you are saying.
Subject
Views
Written By
Posted
6345
February 04, 2009 10:22PM
3107
February 05, 2009 10:26PM
2948
February 09, 2009 02:50AM
2706
February 09, 2009 11:44PM
2928
February 10, 2009 02:03AM
2593
February 11, 2009 01:21AM
2783
February 11, 2009 08:37PM
Re: use index on a view
6108
February 11, 2009 09:17PM
Sorry, you can't reply to this topic. It has been closed.
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.