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.
UPDATE
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 toSELECT FROM [subselect as view] WHERE ...
in execution. So plans differs.VIEW
version runs faster? My impression is that aVIEW
is syntactic sugar, not a performance booster. And the differences you are finding may highlight the failure to optimize a view as well.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.