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 only takes a minute to sign up.
Sign up to join this community
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.
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 !!!
@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
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 |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
select * from <view_name>will match?