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