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
SELECT STRAIGHT_JOIN.
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
results.
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 : http://www.iasf-milano.inaf.it/~lucio/personal.html
-----------------------------------------------------------------------
() ascii ribbon campaign - against html mail
/\ http://arc.pasp.de/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
