Original question: If I have a view:
CREATE VIEW foo AS SELECT * FROM bar WHERE bar.deleted = 0 are there any queries which perform slower ie.
SELECT ... FROM bar ... WHERE bar.deleted = 0 AND ... vs
SELECT ... FROM foo. In yet another way, is it always just a simple query rewrite?
This is a generic question and I do not have a specific table to show because I am trying to help with designing a framework.
Edit: the MySQL handbook has this under 126.96.36.199.3:
As of MySQL 5.7.6, the optimizer handles derived tables and view references the same way: It avoids unnecessary materialization whenever possible, which enables pushing down conditions from the outer query to derived tables and produces more efficient execution plans. Before MySQL 5.7.6, derived tables were always materialized, whereas equivalent view references were sometimes materialized and sometimes merged. This inconsistent treatment of equivalent queries could lead to performance problems: Unnecessary derived table materialization takes time and prevents the optimizer from pushing down conditions to derived tables.
Does anyone have examples where view references were sometimes materialized with the simple VIEW definition I've given (all fields select from a single table with a constant filter on a single integer field)? Or that only happened with more complex VIEW definitions? That is exactly what I am after.