Does index work with view?

Question

Assume that I have two tables:

table1(ID, attribute1, attribute2) and

table2(ID, attribute1, attribute2) with ID is primary key of two table

and I have a view:

create view myview as
select ID, attribute1, attribute2 from table1
union
select ID, attribute1, attribute2 from table1

Can I use advantage of index of primary key (in sql in general and for mysql in my case), when I execute query like following query ?

select * from myview where ID = 100

Answer 1

It depends on your query. Using a view may limit the indexes that can be used efficiently.

For example using a table I have handy I can create a view using 2 UNIONed selects each with a WHERE clause.

CREATE VIEW fred AS
SELECT *
FROM item
WHERE code LIKE 'a%'
UNION SELECT *
FROM item
WHERE mmg_code LIKE '01%'

Both the code and the mmg_code fields have indexes. The table also has id as a primary key (highest value is about 59500).

As a query I can select from the view, or do a query similar to the view, or I can use an OR (all 3 should give the same results). I get 3 quite different EXPLAINs:-

SELECT *
FROM item
WHERE id > 59000
AND code LIKE 'a%'
UNION SELECT *
FROM item
WHERE id > 59000
AND  mmg_code LIKE '01%';

gives and EXPLAIN of

id      select_type     table       type    possible_keys                                                       key         key_len ref     rows    Extra
1       PRIMARY         item        range   PRIMARY,code,id,id_mmg_code,id_code,code_id                         PRIMARY     4       NULL    508     Using where
2       UNION           item        range   PRIMARY,id,mmg_code,id_mmg_code,id_code,mmg_code_id                 PRIMARY     4       NULL    508     Using where
NULL    UNION RESULT    <union1,2>  ALL     NULL                                                                NULL        NULL    NULL    NULL    Using temporary

while the following

SELECT *
FROM item 
WHERE id > 59000
AND (code LIKE 'a%'
OR mmg_code LIKE '01%');

gives and EXPLAIN of

id      select_type     table       type    possible_keys                                                       key         key_len ref     rows    Extra
1       SIMPLE          item        range   PRIMARY,code,id,mmg_code,id_mmg_code,id_code,code_id,mmg_code_id    PRIMARY     4       NULL    508     Using where

and the following

SELECT *
FROM fred
WHERE id > 59000;

gives and EXPLAIN of

id      select_type     table       type    possible_keys                                                       key         key_len ref     rows    Extra
1       PRIMARY         <derived2>  ALL     NULL                                                                NULL        NULL    NULL    4684    Using where
2       DERIVED         item        range   code,code_id                                                        code        34      NULL    1175    Using index condition
3       UNION           item        range   mmg_code,mmg_code_id                                                mmg_code    27      NULL    3509    Using index condition
NULL    UNION RESULT    <union2,3>  ALL     NULL                                                                NULL        NULL    NULL    NULL    Using temporary

As you can see as indexes have been used in the view it has affected the indexes which can be used when selecting from the view.

The best index is potentially the primary key, but the view doesn't use this.

Answer 2

"Can I use advantage of index of primary key (in sql in general and for mysql in my case), when I execute query like following query?"

MySQL will consider using indexes that have been defined on the underlying tables. However you cannot create an index on the view. Check link mysql Restrictions on Views for further explanation.

Using mysql explain on a query using the view will show the keys being considered under the "possible_keys" column.

EXPLAIN select * from myview where ID = 100;