I have a view v which involves a couple of sub-selects in its SELECT clause plus a couple of joins by id or indexed column.

The view query is like:

SELECT col1, col2, (SELECT ... FROM subt) AS col3 FROM t1 JOIN t2 ON [..]

The fact is that if I query that view by col1, which is pk of t1:

SELECT * FROM v WHERE col1='some value'

the EXPLAIN shows a bunch of DERIVED tables, but if I run the same query on the view SELECT statement:

SELECT col1, col2, (SELECT ... FROM subt) AS col3 FROM t1 JOIN t2 ON [..] WHERE col1='some value'

those same tables show up as SIMPLE.

How's that? I need to fix that view because it is not using some indices (although all joins are by pk or indexed columns), but I'm stuck on those DERIVED rows and I can't go further on my analysis.


For comparison, I've run a couple more explains on a view that doens't show these different execution plans for the same WHERE clause.

Given the view v1 create with CREATE VIEW v1 AS v_select_stmt, the plan for the query:

SELECT * FROM v_crm_v1 v WHERE v.id='some value'

gives all plan steps with select_type=simple.

The plan for:

[v_select_stmt] AND id='some value'

yields all the steps with select_type=simple too (and they're identical to the previuos query plan).

Finally, in the plan for:

SELECT * FROM ( [select_stmt] ) v WHERE id='some value'

the steps have all select_type=derived.

  • SELECT ... FROM ( SELECT FROM [subselect as view] ) WHERE ... is not equivalent to SELECT FROM [subselect as view] WHERE ... in execution. So plans differs.
    – Akina
    Dec 14 '18 at 11:11
  • @Akina I see your point and (not aiming at contradicting you :-)) I've done some more tests. Please see my update.
    – watery
    Dec 14 '18 at 11:32
  • Do you have any case where the VIEW version runs faster? My impression is that a VIEW is syntactic sugar, not a performance booster. And the differences you are finding may highlight the failure to optimize a view as well.
    – Rick James
    Dec 14 '18 at 18:45
  • @RickJames Of course not, a view is not a performance optimization (generally at least, maybe materialized views could be); the point here is that a view should not be slower than a regular query aiming at giving the same results - so yes it is syntactic sugar and it also makes the above software layer easier to write. Thus, my expectation is to write a view with the same performance of a query and I'm looking for pointers at how to investigare where the problem arises in my case.
    – watery
    Dec 16 '18 at 10:37
  • @watery - When VIEWs were added, it seemed to be done with an attitude of "OK, now MySQL has Views". Later came the concept of Merge versus Template Algorithms. As recently as 5.7.6, I see a changelog entry saying "consistent optimization to merge vs materialize in derived table vs VIEW". 5.7.7 seemed to first allow a derived table in a View.
    – Rick James
    Dec 16 '18 at 16:34

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.