| Subject | Re: [firebird-support] select parent based on multiple child conditions |
|---|---|
| Author | setysvar |
| Post date | 2015-12-19T20:09:07Z |
Den 19.12.2015 20:38, skrev 'Daniel Miller' dmiller@...
[firebird-support]:
query include more tables, then you should normally try to put your LEFT
JOINs after your [inner] JOINs. That way, the optimizer has a greater
choice regarding which plan to build. There are of course exceptions to
this, sometimes you may prefer the LEFT JOIN before normal inner JOIN
for optimization purposes, and sometimes there's no option but to have
the LEFT JOIN before the JOIN (e.g. with
SELECT ... FROM CHILD LEFT JOIN FATHER F ... LEFT JOIN MOTHER M ... JOIN
GRANDPARENT GP on GP.ID IN (M.FATHER, M.MOTHER, F.FATHER, M.MOTHER)
there's no way to rearrange so that the JOIN to GRANDPARENT gets before
the LEFT JOINs of FATHER or MOTHER ). But archiving alternative 1 and go
with alternative 2 and then compare them at a later time sounds like a
good plan.
cancer. For a table containing breast cancer, an index on GENDER would
be useful when trying to find men, but not women, since 99% of breast
cancers occur amongst women. Hence, if only 1% have r.NETCON>5 or
r.QUALITY>3, then an index could in some cases be of benefit, although I
doubt it would be useful in your case since NODE_ID probably is rather
selective already.
Set
[firebird-support]:
> With correction for one or two typos - those both worked, thank you!Great to hear, Daniel!
> Had to read, and read, and read - to try to understand HOW these work.
> Which was exactly what I was hoping for - to better understand usage of
> Firebird SQL.
> Looking at the query plans, it appears Alternative 2 - which uses theThe performance of these two ought to be similar. Though if your real
> two exists(select) constructs instead of two joins appears to be
> slightly more efficient - at least the query plan appears slightly
> smaller. So I'll file Alternate 1 for future testing against a larger
> dataset - and use Alternate 2 for now.
query include more tables, then you should normally try to put your LEFT
JOINs after your [inner] JOINs. That way, the optimizer has a greater
choice regarding which plan to build. There are of course exceptions to
this, sometimes you may prefer the LEFT JOIN before normal inner JOIN
for optimization purposes, and sometimes there's no option but to have
the LEFT JOIN before the JOIN (e.g. with
SELECT ... FROM CHILD LEFT JOIN FATHER F ... LEFT JOIN MOTHER M ... JOIN
GRANDPARENT GP on GP.ID IN (M.FATHER, M.MOTHER, F.FATHER, M.MOTHER)
there's no way to rearrange so that the JOIN to GRANDPARENT gets before
the LEFT JOINs of FATHER or MOTHER ). But archiving alternative 1 and go
with alternative 2 and then compare them at a later time sounds like a
good plan.
> I'm assuming there would be no benefit, and would probably be worse, toThat's next to impossible for me to guess. I often relate this to
> add indexes for ROUTE_INDEX, or NETCON, or QUALITY? Because they
> represent a small range of possible values that is duplicated for each
> node?
cancer. For a table containing breast cancer, an index on GENDER would
be useful when trying to find men, but not women, since 99% of breast
cancers occur amongst women. Hence, if only 1% have r.NETCON>5 or
r.QUALITY>3, then an index could in some cases be of benefit, although I
doubt it would be useful in your case since NODE_ID probably is rather
selective already.
Set