The first thing the MariaDB optimizer does is to merge the <<fixed>>VIEW<</fixed>>
definition into the query to obtain:
<<code lang=mysql inline=false>>
select ACRAT_rating
from
ac_anchor
left join ac_name on ac_anchor.AC_ID=ac_name.AC_ID
left join ac_dob on ac_anchor.AC_ID=ac_dob.AC_ID
left join ac_rating on (ac_anchor.AC_ID=ac_rating.AC_ID and
ac_rating.ACRAT_fromdate =
(select max(sub.ACRAT_fromdate)
from ac_rating sub where sub.AC_ID = ac_rating.AC_ID))
where
ACNAM_name='Gary Oldman'
<</code>>
It's important to realize that the obtained query has a useless part:
* <<fixed>>left join ac_dob on ac_dob.AC_ID=...<</fixed>> will produce exactly
one matching record:
** <<fixed>>primary key(ac_dob.AC_ID)<</fixed>> guarantees that there will be
at most one match for any value of <<fixed>>ac_anchor.AC_ID<</fixed>>,
** and if there won't be a match, <<fixed>>LEFT JOIN<</fixed>> will generate a
NULL-complemented “row”
* and we don't care what the matching record is, as table
<<fixed>>ac_dob<</fixed>> is not used anywhere else in the query.
This means that the <<fixed>>left join ac_dob on ...<</fixed>> part can be
removed from the query and this is what Table Elimination module does. The
detection logic is rather smart, for example it would be able to remove the
<<fixed>>left join ac_rating on ...<</fixed>> part as well, together with the
subquery (in the above example it won't be removed because ac_rating used in
the selection list of the query). The Table Elimination module can also handle
nested outer joins and multi-table outer joins.
== See also ==
* This page is based on the following blog post about table elimination:
http://s.petrunia.net/blog/?p=58
