Mysql index on view not working

Question

I created a view named 'myview' as below.

create view myview
as select 'a' source,col1,col2
     from table_a
   union
   select source,col1,col2
     from table_b
;

table_a has an index on col1, table_b has an index on source, col1. When I query on myview as below, no index is used.

select *
  from myview
 where source = a
   and col1 = 'xxx'
 ;

How do I make the indexes work on this query?

Create Code

CREATE TABLE `table_a` (
    `col1` VARCHAR(50) NULL DEFAULT NULL,
    `col2` VARCHAR(50) NULL DEFAULT NULL,
    INDEX `table_a_idx01` (`col1`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

CREATE TABLE `table_b` (
    `source` VARCHAR(50) NULL DEFAULT NULL,
    `col1` VARCHAR(50) NULL DEFAULT NULL,
    `col2` VARCHAR(50) NULL DEFAULT NULL,
    INDEX `table_b_idx01` (`source`, `col1`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

create view myview
as select 'a' source,col1,col2
     from table_a
   union
   select source,col1,col2
     from table_b  

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

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

Explain

explain
select *
  from table_a
 where col1 = 'test'

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,table_a,ref,table_a_idx01,table_a_idx01,153,const,5,Using index condition

explain
select *
  from table_b
 where source = 'b'
   and col1 = 'test'

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,table_b,ref,table_b_idx01,table_b_idx01,306,const,const,1,Using index condition

Explain on myview

explain
select *
  from myview
 where source = 'b'
   and col1 = 'test'

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ref,<auto_key0>,<auto_key0>,306,const,const,1,Using where
2,DERIVED,table_a,ALL,\N,\N,\N,\N,6,\N
3,UNION,table_b,ALL,\N,\N,\N,\N,6,\N
\N,UNION RESULT,<union2,3>,ALL,\N,\N,\N,\N,\N,Using temporary

As you can see, no index is adjusted on view selection.

score 2 · Accepted Answer · edited May 23 '17 at 12:14

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.

Keep in mind that such a materialized view is not keep in sync with the raw data; you have to manually initiate the sync Philipp mentioned. — Rick James, Jul 16 '16 at 00:41
Thaks for your prompt reply. As you know , i do not want to make an index on view, I am just wondering why table_a and table_b's index is not work. — vingorius, Jul 19 '16 at 06:31
@Philipp I also added more information for your better understanding. Thanks. — vingorius, Jul 20 '16 at 08:00
Alright, so I understood the scenario correctly. But that means, to fix your problem you would have to use the workaround I mentioned in the beginning of my answer, or try to use mysql version 5.7 and see if it works there. — Philipp, Jul 20 '16 at 08:23
In real world, why we use index on table, that is because table is too big so make query better performance. To make an another table based on view, it'll take an acceptable times. What if CRUD operations occures frequently on the base table, It can not acceptable. — vingorius, Jul 20 '16 at 08:52
That is true. So another option would be not to use a view for this query, by using the query directly mysql would be able to use the indexes again. — Philipp, Jul 20 '16 at 09:37
Can you accept the answer, so others with the same problem can be directed to this thread — Philipp, Jul 21 '16 at 05:28

Mysql index on view not working

Create Code

Explain

Explain on myview

As you can see, no index is adjusted on view selection.

1 Answers1