MySQL Forums
Forum List  »  Performance

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.

Options: ReplyQuote


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.