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

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.

  • Are those indexes used by this query? Usually it would require deleted column to be first in the index. Please post output of EXPLAIN for this query
    – Stoleg
    Aug 18, 2016 at 13:01
  • I rewrote the question to be more clear.
    – chx
    Aug 18, 2016 at 13:15
  • Regression between what version and what version?
    – Rick James
    Aug 18, 2016 at 20:28
  • Still not clear enough. Let's see SHOW CREATE TABLE, SHOW CREATE VIEW and EXPLAIN SELECT ....
    – Rick James
    Aug 18, 2016 at 20:30
  • Amended the question with an almost adequate quote from the MySQL handbook. All it needs is an example.
    – chx
    Aug 18, 2016 at 20:49


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.