MySQL Forums
Forum List  »  Optimizer & Parser

Re: Can we expect an enhancement for derived table merge optimization?
Posted by: Rick James
Date: January 13, 2013 12:24PM

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.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.