Skip navigation links

MySQL Forums :: Optimizer & Parser :: Can we expect an enhancement for derived table merge optimization?


Advanced Search

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


Subject Views Written By Posted
Can we expect an enhancement for derived table merge optimization? 1754 Michael Hogue 01/04/2013 12:04PM
Re: Can we expect an enhancement for derived table merge optimization? 654 Rick James 01/07/2013 10:50PM
Re: Can we expect an enhancement for derived table merge optimization? 871 Roy Lyseng 01/11/2013 09:18AM
Re: Can we expect an enhancement for derived table merge optimization? 633 Rick James 01/13/2013 12:24PM
Re: Can we expect an enhancement for derived table merge optimization? 701 Roy Lyseng 01/14/2013 08:43AM
Re: Can we expect an enhancement for derived table merge optimization? 598 Rick James 01/14/2013 09:54AM
Re: Can we expect an enhancement for derived table merge optimization? 618 Roy Lyseng 01/15/2013 06:15AM
Re: Can we expect an enhancement for derived table merge optimization? 588 Roy Lyseng 01/11/2013 08:51AM


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.