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

[Roy Lyseng]

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.

Thanks,
Roy Lyseng
MySQL optimizer team.