List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:September 3 2007 4:41pm
Subject:Re: excessive time spent in "statistics" status
View as plain text  
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


Thread
excessive time spent in "statistics" statusLucio Chiappetti31 Aug
  • Re: excessive time spent in "statistics" statusShawn Green1 Sep
    • Re: excessive time spent in "statistics" statusLucio Chiappetti3 Sep
      • Re: excessive time spent in "statistics" statusShawn Green3 Sep
        • Re: excessive time spent in "statistics" statusLucio Chiappetti4 Sep
          • Re: excessive time spent in "statistics" statusLucio Chiappetti4 Sep
            • Re: excessive time spent in "statistics" statusShawn Green4 Sep
              • Re: excessive time spent in "statistics" statusLucio Chiappetti5 Sep
        • Re: excessive time spent in "statistics" statusJeremy Cole14 Sep
          • Re: excessive time spent in "statistics" statusLucio Chiappetti14 Sep
          • Re: excessive time spent in "statistics" statusPete Harlan17 Sep
            • Re: excessive time spent in "statistics" statusBaron Schwartz17 Sep