Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a schema with a number of views. I need to check the execution plans to make sure the appropriate indexes are in place and being used.

How do I do this?

I'd rather not have to copy and paste the output from show create view <viewname> into explain, especially as some of the views are built on top of other views and this would be quite a pain.

share|improve this question
1  
Note that the execution plan may be different when the VIEW is used in real-life queries, as it depends on the WHERE and other clauses in the query selecting from the VIEW. Although MySQL is fairly bad at optimizing VIEWs, it does have some optimizations where conditions are pushed down for example. –  Jannes Mar 22 '12 at 13:02
    
@Jannes good point, I didn't consider that aspect. Is it safe to assume that the execution plan from select * from <view_name> will match? –  Matt Fenwick Mar 22 '12 at 13:52
1  
Yeah as far as i know. If you want mysql to use an index on field1 in select * from <view-name> where field1=10 then you have to keep the view really simple. No GROUP BY or UNION for example. I guess you could say the execution plan you are looking at is sort of the worst case situation in that it could only get better if mysql finds an optimization to use. –  Jannes Mar 23 '12 at 1:52
add comment

2 Answers

Use the information_schema.views table

This will generate the EXPLAIN for all views

mysql -uroot -p -AN -e"select concat('explain ',view_definition) from information_schema.views" > /root/ExplainViews.sql

This will generate the EXPLAIN for all views in the mydb database

mysql -uroot -p -AN -e"select concat('explain ',view_definition) from information_schema.views where table_schema = 'mydb'" > /root/ExplainViews.sql

Give it a Try !!!

UPDATE 2012-03-22 11:30 EDT

@MattFenwick, your answer is a whole lot simpler than mine. Here is an example I tried out on my PC running MySQL 5.5.12. I ran EXPLAIN on both the SELECT version from your answer and the EXPLAIN generated from my answer:

mysql> explain select * from bigjoin;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | k     | index  | NULL          | PRIMARY | 4       | NULL          |   14 | Using index |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.k.id_key |    1 | Using index |
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    4 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

mysql> explain select `a`.`id_key` AS `id_key1`,`b`.`id_key` AS `id_key2` from ((`test`.`idlist` `k` left join `test`.`id_key_table` `a` on((`k`.`id_key` = `a`.`id_key`))) left join `test`.`new_keys_to_load` `b` on((`k`.`id_key` = `b`.`id_key`)));
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | k     | index  | NULL          | PRIMARY | 4       | NULL          |   14 | Using index |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.k.id_key |    1 | Using index |
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    4 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
3 rows in set (0.00 sec)

mysql>

They both produced the same EXPLAIN plan. I will change my answer to implement your way. You get a +1 from me although it's +2 for simplicity. You should go ahead and accept your own answer on this one.

Here is an interesting factoid about VIEWs in MySQL : A view is represented in two places in the information_schema database

This will generate the EXPLAIN for all views

mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.tables WHERE engine IS NULL" > /root/ExplainViews.sql

or

mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.views" > /root/ExplainViews.sql

This will generate the EXPLAIN for all views in the mydb database

mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.tables WHERE table_schema='mydb' AND engine IS NULL;" > /root/ExplainViews.sql

or

mysql -uroot -p -AN -e"select concat('explain select * from ',table_schema,'.',table_name,';') from information_schema.views WHERE table_schema='mydb';" > /root/ExplainViews.sql
share|improve this answer
    
+1 for showing that the execution plan is the same both ways! –  Matt Fenwick Mar 22 '12 at 18:58
add comment
up vote 3 down vote accepted

This is what I first tried:

mysql> explain view_name;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| field1  | varchar(3) | YES  |     | NULL    |       |
| field2  | varchar(3) | YES  |     | NULL    |       |
| field3  | bigint(21) | NO   |     | 0       |       |
| field4  | bigint(21) | NO   |     | 0       |       |
+---------+------------+------+-----+---------+-------+

Obviously this doesn't work -- it's the same as doing describe view_name.

However, select * from view_name seems to work:

mysql> explain select * from view_name;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived5> | ALL  | NULL          | NULL | NULL    | NULL |   18 |                                 |
|  1 | PRIMARY     | <derived3> | ALL  | NULL          | NULL | NULL    | NULL |  105 | Using where; Using join buffer  |
|  5 | DERIVED     | <derived6> | ALL  | NULL          | NULL | NULL    | NULL |   68 | Using temporary; Using filesort |
|  6 | DERIVED     | basetable  | ALL  | NULL          | NULL | NULL    | NULL |  928 | Using temporary; Using filesort |
|  3 | DERIVED     | <derived4> | ALL  | NULL          | NULL | NULL    | NULL |  386 | Using temporary; Using filesort |
|  4 | DERIVED     | basetable  | ALL  | NULL          | NULL | NULL    | NULL |  928 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
share|improve this answer
    
+1 for a clearly, simpler answer. I adjusted my answer based on yours. You should accept your answer on this one. –  RolandoMySQLDBA Mar 22 '12 at 15:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.