List:General Discussion« Previous MessageNext Message »
From:Lucio Chiappetti Date:September 4 2007 1:34pm
Subject:Re: excessive time spent in "statistics" status
View as plain text  
On Tue, 4 Sep 2007, Lucio Chiappetti wrote:

> I'll do some experimenting and report back.

In lack of better ways of doing a tie-break, I've done the following 
tests (with the linemode client), checking both the results of a query and 
the total time spent. I tested 16 different combinations of arbitrary 
WHERE conditions and correlation with an arbitrary external tables, using 
my "maximal" G (the one with 26 members).

For each I tested 3 cases (total 16*3=48) :

 a) the query on the "virtual" table correlated with the external
    (the virtual is my G left join t1 ... left join tn). This involves
    the lowest number of joins (29) and is of course the fastest (and 
    also the easiest to get access to "hidden" columns by name, but
    is not what I intend our users use because the way to select columns
    is clumsy (they should use a VIEW on the 26 members instead ... 
    normally with no correlation on external that will be equally fast), 
    but represents my REFERENCE, i.e. I checked the results of the other
    test to be the same as this.  

 b) the query on the VIEW V with "members also" (which implies a redundant
    join of V with G left join t1 ... left join tn) and correlated with
    an external table. Doubles the joins (59). I used here

 c) the same query of (b) but with a normal select, and preceded by
    setting optimizer_search_depth=0 (auto).

I did each test in a fresh mysql session to prevent cached valued to 
affect the timings.

The good news are that all query go to end without getting stuck in the 
statistics state, and that (a),(b),(c) for the same query return the same 

Considering the timing instead :

 (a) are obviously the fastest (from 0.0 to 0.63 seconds at worst)

 (b) the queries with SELECT STRAIGHT_JOIN ("not optimized" ?) are
     *in general* the slowest but not too slow, i.e. complete within from 
     0.8 to 1.9 sec. There are however a couple of cases involving one 
     external table in which they are slower (though not unfeasible),
     from 3 to 12 sec in one case, and from 24 to 45 sec in another.

 (c) the queries with optimizer_search_depth=0 have a speed comparable
     with (b). *In general* they are marginally faster than (b) (0.7
     to 1.6 sec) when (b) is reasonably fast.  When (b) is slow,
     however (c) is TWICE AS SLOWER (5 to 23 and 48 to 89 sec)

The explain select does not give obvious clues while those particular 
table combinations are slower, and, considered that the queries involving 
"view + member also + external" concern an absolute minority of users [*] 
it is not worth spending more time investigating.

[*] actually a larger minority (I hope it remains such) of our "public"
users accesses the DB once to "take away" all data without performing any 
selection  :-(

My inclination therefore would be to prefer optimizer_search_depth=0
to SELECT STRAIGHT_JOIN *if* it can be easily arranged in our tomcat 
environment, because of the marginal increase in speed despite the 
occasional worsening. But the two look almost equivalent.

Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info :
()  ascii ribbon campaign - against html mail 
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