MySQL Forums
Forum List  »  Optimizer & Parser

Can we expect an enhancement for derived table merge optimization?
Posted by: Michael Hogue
Date: January 04, 2013 12:04PM

In MySql 5.5 (and 5.6), derived tables are materialized before any helpful where clauses on the containing query are merged in.

For example:

select 'briefing_story_id '
from (select briefing_story_id, created_user_id
from briefing_story where briefing_story.story_type = 'CLIP') as inner_select
where inner_select.created_user_id = 12;

expands the derived table using only the where clause presented on derived table: briefing_story.story_type = 'CLIP'. Often times, the surrounding query adds on a much better filter that might lead to an index being the obvious path: inner_select.created_user_id = 12.

MariaDb claims to already have this optimization, and calls it "derived table merge".

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.

My question is really twofold:

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?

As a side-note, I'm asking asking because we're using an O-R Mapping Tool (Microsoft's Entity Framework) combined with the MySql Connector. Together, they make heavy use of derived tables in their generated SQL.

Thanks greatly.

Edited 1 time(s). Last edit at 01/04/2013 12:05PM by Michael Hogue.

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.