How to Have an Indexed View in MySQL

How do I get MySQL to use an INDEX for view query?

How do you get MySQL to use an index for a view query? The short answer, provide an index that MySQL can use.

In this case, the optimum index is likely a "covering" index:

... ON highscores (player, happened_in, score)

It's likely that MySQL will use that index, and the EXPLAIN will show: "Using index" due to the WHERE player = 24 (an equality predicate on the leading column in the index. The GROUP BY happened_id (the second column in the index), may allow MySQL to optimize that using the index to avoid a sort operation. Including the score column in the index will allow the query to satisfied entirely from the index, without having to visit (lookup) the data pages referenced by the index.

That's the quick answer. The longer answer is that MySQL is very unlikely to use an index with leading column of happened_id for the view query.

---

Why the view causes a performance issue

One of the issues you have with the MySQL view is that MySQL does not "push" the predicate from the outer query down into the view query.

Your outer query specifies WHERE happened_in = 2006. The MySQL optimizer does not consider the predicate when it runs the inner "view query". That query for the view gets executed separately, before the outer query. The resultset from the execution of that query get "materialized"; that is, the results are stored as an intermediate MyISAM table. (MySQL calls it a "derived table", and that name they use makes sense, when you understand the operations that MysQL performs.)

The bottom line is that the index you have defined on happened_in is not being used by MySQL when it rusn the query that forms the view definition.

After the intermediate "derived table" is created, THEN the outer query is executed, using that "derived table" as a rowsource. It's when that outer query runs that the happened_in = 2006 predicate is evaluated.

Note that all of the rows from the view query are stored, which (in your case) is a row for EVERY value of happened_in, not just the one you specify an equality predicate on in the outer query.

The way that view queries are processed may be "unexpected" by some, and this is one reason that using "views" in MySQL can lead to performance problems, as compared to the way view queries are processed by other relational databases.

---

Improving performance of the view query with a suitable covering index

Given your view definition and your query, about the best you are going to get would be a "Using index" access method for the view query. To get that, you'd need a covering index, e.g.

... ON highscores (player, happened_in, score).

That's likely to be the most beneficial index (performance wise) for your existing view definition and your existing query. The player column is the leading column because you have an equality predicate on that column in the view query. The happened_in column is next, because you've got a GROUP BY operation on that column, and MySQL is going to be able to use this index to optimize the GROUP BY operation. We also include the score column, because that is the only other column referenced in your query. That makes the index a "covering" index, because MySQL can satisfy that query directly from index pages, without a need to visit any pages in the underlying table. And that's as good as we're going to get out of that query plan: "Using index" with no "Using filesort".

---

Compare performance to standalone query with no derived table

You could compare the execution plan for your query against the view vs. an equivalent standalone query:

SELECT player
     , MAX(score) AS highest_score
     , happened_in
 FROM highscores
WHERE player = 24
  AND happened_in = 2006
GROUP
   BY player
    , happened_in

The standalone query can also make use of a covering index e.g.

... ON highscores (player, happened_in, score)

but without a need to materialize an intermediate MyISAM table.

---

I am not sure that any of the previous provides a direct answer to the question you were asking.

Q: How do I get MySQL to use an INDEX for view query?

A: Define a suitable INDEX that the view query can use.

The short answer is provide a "covering index" (index includes all columns referenced in the view query). The leading columns in that index should be the columns that are referenced with equality predicates (in your case, the column player would be a leading column because you have a player = 24 predicate in the query. Also, the columns referenced in the GROUP BY should be leading columns in the index, which allows MySQL to optimize the GROUP BY operation, by making use of the index rather than using a sort operation.

The key point here is that the view query is basically a standalone query; the results from that query get stored in an intermediate "derived" table (a MyISAM table that gets created when a query against the view gets run.

Using views in MySQL is not necessarily a "bad idea", but I would strongly caution those who choose to use views within MySQL to be AWARE of how MySQL processes queries that reference those views. And the way MySQL processes view queries differs (significantly) from the way view queries are handled by other databases (e.g. Oracle, SQL Server).

Is it possible to have an indexed view in MySQL?

I don't think MySQL supports materialized views which is what you would need, but it wouldn't help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would still cause the write speed issues.

Your best bet would probably be to create summary tables that get updated periodically.

How to see indexes for a database or table in MySQL?

To see the index for a specific table use SHOW INDEX:

SHOW INDEX FROM yourtable;

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

Removing the where clause will show you all indexes in all schemas.

Mysql index on view not working

You can not create an index on a view: http://dev.mysql.com/doc/refman/5.7/en/view-restrictions.html, so you have to hope the index is used. https://stackoverflow.com/a/7922711/3595565

Workaround

There is a workaround mentioned in the comments of another part of the documentation: https://dev.mysql.com/doc/refman/5.5/en/create-view.html In which you create a regular table and set your specialized index and load the data from the view into the table.

Creating a materialized view emulation as described above looks good,
the only problem is that we are inheriting the lack of indexes MySQL
views expose.

My solution is to create a correctly indexed table according to my
needs, having the exact same structure as the view, and then running
something like this:

LOCK TABLES materializedView WRITE; 
TRUNCATE materializedView; 
INSERT INTO materializedView SELECT * FROM regularView;
UNLOCK TABLES;

That way all indexes from materializedView are preserved on every
"refresh".

I'm planning to use this in an application I'm doing right now, where
we will have a lot more SELECTs than inserts/updates. If I keep a
regular view for my SELECTs, I'll be asking the server to make tons of
calculations every time someone needs to know how many items are on
stock for product "A", instead, I'll have all SELECTs towards the
"materializedView" with correct SKU, Store and Period indexes.

The view "refresh" will occur every time someone runs an INSERT or
UPDATE, which will be on a 20 to 1 ratio. (20 Selects for every Update
or Insert)

I hope things go as smooth as I'm planning. Greetings ;-)

Why does your Query not make use of the indexes?

When using UNION in a SELECT mysql creates a temporary table to save the data. Thus as a view is a 'shortcut' for your more complex query, when calling the select it will again execute the union, use a temporary table... use the temptable alghorithm to process the data.

Checking the manual again: http://dev.mysql.com/doc/refman/5.7/en/view-restrictions.html

Indexes can be used for views processed using the merge algorithm.
However, a view that is processed with the temptable algorithm is
unable to take advantage of indexes on its underlying tables (although
indexes can be used during generation of the temporary tables).

Conclusion: The UNION in your query hinders the view from using the indexes.

Sources

question in mysql forum for the same problem answer:

I guess the union causes the view to use the temptable algorithm, it creates a temporary table and then apply the where condition to the temporary table.

bugreport "DO NOT CREATE TEMPORARY TABLES FOR UNION ALL"

Currently, union queries always use a temporary table to store the
result before it is returned to the user. [...]

Fixed in MySQL 5.7 http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html

The server no longer uses a temporary table for UNION statements that
meet certain qualifications. Instead, it retains from temporary table
creation only the data structures necessary to perform result column
typecasting.[...]

Some testdata to check the profiler

CREATE TABLE test1 (
    id int auto_increment PRIMARY KEY,
  col1 varchar(50),
  col2 varchar(50)
);

CREATE TABLE test2 (
    id int auto_increment PRIMARY KEY,
  col1 varchar(50),
  col2 varchar(50)
);

INSERT INTO test1 (col1, col2) 
VALUES 
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2');


INSERT INTO test2 (col1, col2) 
VALUES 
('test2', 'testcol2'),
('test2', 'testcol2'),
('test2', 'testcol2'),
('test2', 'testcol2'),
('test2', 'testcol2'),
('test2', 'testcol2');

CREATE VIEW testview AS
SELECT * FROM test1
UNION
SELECT * FROM test2;

Check the profiler:

SET PROFILING = 1;
SELECT * FROM testview WHERE id = 1;
+----+-------+----------+
| id | col1  | col2     |
+----+-------+----------+
|  1 | test  | testcol2 |
|  1 | test2 | testcol2 |
+----+-------+----------+
SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000017 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000029 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000121 |
| System lock                    | 0.000012 |
| checking permissions           | 0.000014 |
| checking permissions           | 0.000032 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000007 |
| preparing                      | 0.000006 |
| executing                      | 0.000003 |
| Sending data                   | 0.000046 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000004 |
| preparing                      | 0.000003 |
| executing                      | 0.000002 |
| Sending data                   | 0.000023 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000003 |
| preparing                      | 0.000003 |
| executing                      | 0.000002 |
| Sending data                   | 0.000008 |
| removing tmp table             | 0.000005 |
| Sending data                   | 0.000005 |
| Waiting for query cache lock   | 0.000002 |
| Sending data                   | 0.000024 |
| init                           | 0.000011 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000004 |
| preparing                      | 0.000006 |
| executing                      | 0.000002 |
| Sending data                   | 0.000021 |
| end                            | 0.000003 |
| query end                      | 0.000004 |
| closing tables                 | 0.000002 |
| removing tmp table             | 0.000004 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000005 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000013 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000003 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

I can't take too much information out of the profile, but it does mention temporary table, enough (for me) to validate my conclusion.

Does index work with view?

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.

MySQL Views and index use

If you query a view, MySQL will consider using indexes on the underlying tables.

However it is not possible to add a new index to a calculated column in the view. I think this is what people meant by MySQL not having indexed views, as opposed to (for example) SQL Server's indexed views.

---

Related Topics