Lucio Chiappetti wrote:
> 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).
> <snip>
In your b) test, did you use the SELECT STRAIGHT_JOIN as your outer
SELECT statement or within the CREATE VIEW statement>? If you only tried
it one way, you could try it the other, too.
>
> [*] 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 :-(
>
It saddens me to see people abuse your hard work in this way.
> 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.
>
Another option would be to build your view dynamically based on the
criteria that a user selects through a web-based interface.
One other way to approach this project would be to normalize your
relationships and have one skinny but very tall table similar to
CREATE TABLE map_table (
object1_id int
, object1_type int
, object2_id int
, object2_type int
, confidence tinyint
)
where confidence would be a whole number from 0 to 100. You would need
only one of these to replace each G table you are generating now.
However, that would be a major difference in how you currently use your
data and I would not suggest this for a near-term solution.
--
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
