Re: Can we expect an enhancement for derived table merge optimization?

[Rick James]

Roy, thanks for the insights. I am an outsider looking in. I have been using MySQL since 3.53 in 1999. I try to keep up with all the subtle issues, but we are getting into some details I am weak on. What I know comes from conferences; I have avoided VIEWs because of their limitations. It has always felt like VIEWs were implemented hastily and that the Optimizer was not involved enough in the implementation. (As you say: "that derived tables and view management were simply developed in two separate stages".)

> We are working on a solution were we merge simple derived tables into the outer query block
Good to hear.

> Case 2 ... are able to create an index on the derived table

Roy, Is that 5.6.7? Or earlier??

> Case 5 ... a) If the subquery is non-aggregated, it is converted to a semi-join. See below.

If the subquery is complex (eg, including JOINs, etc); "a)" could be significantly slower than "b)". OTOH, if the _outer_ query has some form of LIMIT, then evaluating "a)" a few times could be better than a big materialized subquery, plus indexing. (Perhaps you cover this debate in "Case 6")

Roy, these seem to be contradictory:
> MySQL 5.6 does not include view merging.
> I am not aware of any principal differences between views and regular tables
(Granted, it is a stretch to say those are even related.)
* If a VIEW has a WHERE clause and the SELECT using the VIEW also has a WHERE clause, the WHEREs are not combined before optimizing, correct? (That's what you mean by "merging"?)
* A "regular" table (or more precisely a regular SELECT) would look at the entire WHERE, thereby having more optimization opportunities.

I see 'merging' as the main step toward making VIEWs first-class, competitive, constructs.