MySQL Forums
Forum List  »  Optimizer & Parser

Re: How could this SQL be optimized?
Posted by: Øystein Grøvlen
Date: August 22, 2014 01:54AM

Marion Gordon Wrote:
-------------------------------------------------------
> The first order tried by the optimizer is the
> initial left to right order of the FROM clause.
> The compiler remembers its best result so far
> attained in trying various compilations, so it
> will not explore branches of the table permutation
> tree which, while incomplete, exceed the cost of
> the best complete result so far. Thus a good guess
> of initial table order by the programmer may save
> compilation time.

This is not true for the MySQL optimizer. Traditionally, tables were initially ordered by size with smallest table first. However, in MySQL 5.6 key dependency is also taken into account. That is, if table 'a' has fewer rows that table 'b', but table 'a' can be accessed through index lookup if table 'b' is read first, the initial ordering will be b, a.

For further discussion on this, see http://jorgenloland.blogspot.co.uk/2012/04/improvements-for-many-table-joins-in.html

Regards,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote


Subject
Views
Written By
Posted
2346
May 29, 2014 11:26PM
Re: How could this SQL be optimized?
1141
August 22, 2014 01:54AM


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.