hi.
sorry for the vague syntax used below, but the query is huge so i've
tried to present it in simple terms. And sorry if i'm doing obviously
stupid things, i have lots of years programming behind me but NO sql
involved.
i have a somewhat tree-like structure of objects that link to each
other via many2many associations. it looks like:
(N is "root")N links to R,P,FR links to F P links to O,FO links to O,F #recursivelyF links to Z
All links to F but the one in O are "shortcuts", to avoid looking it
up recursively.
each of these objects has some associated values (again many2many,
ownership).
what i want is to get all the values related to a given N and its
sublevels, in one query.
one variant of what i've invented so far is (~pseudocode, no recursion
on O):
SELECT ownership.*, value.*
FROM Nazn, mm_N2P, mm_P2O, mm_O2O, mm_O2O AS mm_O2O1, mm_N2Z, ownership JOIN value ON ownership.value = value.dbid
WHERE (N.dbid = ownership.NORN.dbid = mm_N2R.left AND mm_N2R.right = ownership.RORN.dbid = mm_N2P.left AND (
mm_N2P.right= ownership.P OR mm_N2P.right = mm_P2O.left AND ( mm_P2O.right = ownership.O OR
mm_P2O.right= mm_O2O.left AND ( mm_O2O.right = ownership.O OR mm_O2O.right =
mm_O2O1.leftAND mm_O2O1.right = ownership.O)))ORNazn.dbid = mm_N2F.left AND ( mm_N2F.right =
ownership.F OR mm_N2Z.right = ownership.Z)
) AND ownership.value = value.dbid AND N.obj = whatever-filter-by-N
----------------
this scales very poor.
it uses the shortcut to F present in N.
for just 200 rows with related associations, it takes 4 seconds to get
result.
if i use the shortcut to F present in P, it takes 2 seconds - but
thats still inacceptable.
seems that the number or consequtive ORs on same level is killing it.
EXPLAIN gives nested loops all over.
What am i doing wrong here?
should i expand the A-to-B links of the sort
mm_N2P.right = mm_P2O.left
into
mm_N2P.right = P.dbid and P.dbid == mm_P2O.left ?
the query is generated via sqlalchemy and a layer on top, so i can
tweak it any way required (and it has many other sub/filterings which
i've ommited for brevity - they dont make it better/worse).
any pointers of how such queries should be written are appreciated -
e.g. what is considered fine, what doable and what is a no-no.
thanks ahead
ciao
svil
www.svilendobrev.com
dbcook.sf.net