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

Normally, a database server is smart enough to use the indexes. You could try to have a look at the execution plan of your query and see whether it uses the indexes. — Robert Kock, May 25 '16 at 08:40
In MySQL no I don't think the index is used when selecting from a view. — Kickstart, May 25 '16 at 08:53
@RobertKock how can i a look at execution plan of my query ? — , May 25 '16 at 08:57
http://postimg.org/image/q8e76nd3v/ this mean it does use primay key right ? — , May 25 '16 at 09:08
For oracle I use sqldeveloper. There should be something similar for mysql. — Robert Kock, May 25 '16 at 09:10
@oopaewem - does it occur to you that by searching on the words `mysql execution plan`, you might find *plenty* of resources that tell you how to obtain one? — Damien_The_Unbeliever, May 25 '16 at 09:14
uhm. in mysql it's explan. thank you. now i have a know a tool to check my query :) — , May 25 '16 at 09:22
@Kickstart - an index **is** used when dealing with views in MySQL. `EXPLAIN` will show that when selecting from view, an index will be used. If indexes weren't used with views, what would be the point of using them? — Mjh, May 25 '16 at 10:03
@Kickstart - the point of the view is readability, it's a copypaste version of a huge query, I don't get your comment at all. You said indexes aren't used but they are. It's irrelevant if you want to admit it, but you supplied false information, that's all there is to it. — Mjh, May 25 '16 at 10:32
Readability affecting performance is an issue. But it is quite easy for the EXPLAINs to differ. — Kickstart, May 25 '16 at 10:38
Well you are wrong, views don't impact performance if the algorithm used is `MERGE`. You are free to believe whatever you want, but at least make sure you're supplying correct information. For some odd reason you still believe that if you add a `WHERE` clause to a view that indexes might not be used. This isn't true, neither for the `create view` statement nor for `select from view` statement. — Mjh, May 25 '16 at 11:58
@Mjh Thank for you comment but you said "the algorithm used is MERGE" what is it about ? I don't what is merge algorithm :( — , May 25 '16 at 14:59
It's related to how MySQL will process the view, since it defines the algorithm used to process the view. You can read about these algorithms on [MySQL manual page](http://dev.mysql.com/doc/refman/5.7/en/view-algorithms.html). — Mjh, May 25 '16 at 15:03

score 1 · Answer 1 · answered May 25 '16 at 09:43

"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;

score 1 · Accepted Answer · answered May 25 '16 at 11:14

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.

A full answer exists elsewhere for this - http://stackoverflow.com/questions/13944946/how-do-i-get-mysql-to-use-an-index-for-view-query — Kickstart, May 25 '16 at 12:10

Does index work with view?

2 Answers2