Is there anything wrong with nested Views in MySQL

Question

Is there anything wrong with having one view reference another view? For example, say I have a users table

CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);

Then for the sake of argument a view that just shows all users

CREATE VIEW all_users AS SELECT * FROM users

And then a view that just returns their first_name and last_name

CREATE VIEW full_names AS SELECT first_name, last_name FROM all_users

Are there performance issue with basing one view off another? Let's also pretend this is the simplest of examples and a real world scenario would be much more complex, but the same general concept of basing one view of another view.

Answer 1

It depends on the ALGORITHM used. TEMPTABLE can be pretty expensive while MERGE should be the same as using the table directly so no loss there.

Answer 2

And for your example is the same:

mysql> explain select * from users;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | users | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.01 sec)

mysql> explain select * from all_users;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | users | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.00 sec)

mysql> explain select * from full_names ;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | users | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.02 sec)