Hello Lucio,
Thank you for the excellent description of you problem. I believe I
completely understand both the data you are handling and the problems
you are facing.
I would not ask you to change your schema at this point. There is far
too much work put into it at this phase to suggest a redesign. What I
hope to do is to help you to make you queries work better by applying
hints, modifiers, and limits to the optimizer.
http://dev.mysql.com/doc/refman/5.0/en/join.html states:
"STRAIGHT_JOIN is identical to JOIN, except that the left table is
always read before the right table. This can be used for those (few)
cases for which the join optimizer puts the tables in the wrong order."
You asked what would happen if you replaced all of your view's LEFT JOIN
clauses with STRAIGHT_JOIN clauses. When used in the FROM clause (as
part of the definitions of where the data comes from) STRAIGHT_JOIN
would be equivalent to a JOIN which is equivalent to an INNER JOIN. This
is not what you are trying to achieve.
However, there is another place you can put the STRAIGHT_JOIN modifier:
in the SELECT clause
(http://dev.mysql.com/doc/refman/5.0/en/select.html) . Quoting again:
" STRAIGHT_JOIN forces the optimizer to join the tables in the order in
which they are listed in the FROM clause. You can use this to speed up a
query if the optimizer joins the tables in non-optimal order. See
Section 6.2.1, “Optimizing Queries with EXPLAIN”. STRAIGHT_JOIN also can
be used in the table_references list. See Section 12.2.7.1, “JOIN Syntax”.
SELECT STRAIGHT_JOIN ....
FROM ...
LEFT JOIN ...
WHERE ...
...
This will allow you to keep your LEFT JOINs in the <table reference>
portion of your query (everything between FROM and WHERE) but avoid all
of the permutations the optimizer performse related to trying to analyze
which table to join first to which other table. Why are some sets of
tables optimized more quickly than the others? My best guess would be
that some mix of WHERE conditions and table indexes make it much faster
to eliminate combinatorial permutations of JOIN sequences than others.
There is another variable you could use to minimize how many table
permutations the optimizer will examine. This is the variable
optimizer_search_depth:
(http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_optimizer_search_depth)
(My apologies if that link wraps incorrectly). You can set this variable
on a per-session basis so it would be possible to tune it for each call
to your views. I know that would be quite the administrative hassle to
implement setting this variable for each call to your views but I
provide it here for completeness.
Please try out "SELECT STRAIGHT_JOIN" and let me know if your situation
improves.
BTW - we encourage everyone to reply to the full list on all responses
(unless they are confidential) so that all members can gain from the
knowledge transfer.
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
