MySQL Forums
Forum List  »  Optimizer & Parser

Re: Can we expect an enhancement for derived table merge optimization?
Posted by: Roy Lyseng
Date: January 11, 2013 08:51AM

Strangely, it seems as though MySql is already handling this same concept when selecting from a view (which seems conceptually like the same problem, but what do I know.) The view is not materialized- instead, any where clauses in the view are combined with the where clauses of the containing query.

This is indeed true. I guess that derived tables and view management were simply developed in two separate stages. A possible workaround for a merged derived table is thus to create a view with the same definition and insert a reference to the view in your query, but I admit this is a bit awkward.

a) Am I correct that this feature does not currently exist in any MySql version? (I tested in 5.5.16 and 5.6.7 and neither merged the where clause.)
b) Is this feature anywhere in the development roadmap, and if so, what is the best guess of when (date and or version) it will be included in MySql?

MySQL 5.6 does not include view merging. However, the materialized table created from the derived table is generated with indexes on fields that match predicates in the outer query, so execution is often improved significantly compared to earlier versions.

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

Roy Lyseng
MySQL optimizer team.

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.