--- In firebird-support@yahoogroups.com, Helen Borrie wrote:

> >SELECT
> > EVers.ID, Elems.nodeID, Elems.ID, EAttribs.MD5
> >FROM Vers
> > JOIN Elems ON EVers.elementId = Elems.ID
> > JOIN ENames ON Elems.nameId = ENames.ID
> > JOIN EAttribs ON EVers.attribsID = EAttribs.ID
> >WHERE
> >(
> > Elems.lastVersionID = EVers.ID AND
> > Elems.nodeID = -9223372036853775797
> >)
> >I get this:
> >
> >PLAN SORT (JOIN (ENAMES NATURAL,ELEMS INDEX (ELEMS_13_ASC),VERS
> >INDEX(RDB$PRIMARY20,VERS_1_ASC),ATTRIBS INDEX (RDB$PRIMARY18)))

Hmm, the names of the indexes aren't exactly telling which fields they
belong to. Here's what I can see from the query.

ELEMS_13_ASC=Elems.nameId
RDB$PRIMARY20=Evers.ID
VERS_1_ASC=EVers.elementId
RDB$PRIMARY18=EAttribs.ID

You want at least two changes to this query. You already know the
first, getting Elems to be the first table in the plan and use the
index I expect you have on Elems.nodeID. The other is to get rid of
VERS_1_ASC in your query.

> >Any idea why this would be doing a natural on ENames? If I put in
> >the primary key index for Enames into the plan, I get an invalid
> >index for query exception.
> >
> >Btw, this is 1.0.3. We are unable to upgrade to 1.5 at this point.
>
> Apart from your typo (Vers? Evers?), the situation here is that
> eNames is doing nothing in this query except acting as a
> retrospective selector for the other tables - that is, the query is
> not interested in any other data in the eNames table except the
> primary key. Since these are all inner joins, what would you lose
> by inverting this query so that the leftmost set is the Elems rows
> that are selected by the join with ENames? In this case, the
> primary key index of ENames will be used.
>
> Also, you have pushed a JOIN criterion into the WHERE clause (see
> Elems.lastVersionID = EVers.ID). Though it's sometimes useful with
> certain outer joins to push a WHERE criterion into the JOIN clause,
> it's never appropriate to relegate a JOIN condition into the WHERE
> clause.
>
> I'll restructure this query using aliases to make it easier to see
> what is doing what to what:
>
> SELECT
> v.ID, e.nodeID, e.ID, a.MD5
> FROM Elems e
> JOIN ENames n ON n.ID = e.nameID
> JOIN EVers v
> ON v.elementId = e.ID AND v.ID = e.lastVersionID
> JOIN EAttribs a ON a.ID = v.attribsID
> WHERE
> e.nodeID = ? /* -9223372036853775797 */

This is sound advice from Helen, as always.

> That relationship between EVers and Elems looks suspiciously
> circular to me, as well...it looks as if it needs resolving with an
> intersection table or, perhaps, for this query, a re-entrant join on
> EVers..are you actually getting the expected output?

Providing that this query is actually what you want, you may do two
small changes (if necessary) to correct the issues I identified above
and get the following plan (or some similar plan with ELEMS as the
first table):

PLAN SORT (JOIN (ELEMS INDEX (ELEMS_nodeID),ENAMES INDEX
(RDB$PRIMARYxx), VERS INDEX(RDB$PRIMARY20,VERS_1_ASC),ATTRIBS INDEX
(RDB$PRIMARY18)))

The changes are

a) Remove the redundant use of VERS_1_ASC

JOIN EVers v ON v.elementId+0 = e.ID AND v.ID = e.lastVersionID

b) Force ELEMS to be the first table in the plan

JOIN ENames n ON n.ID = e.nameID+0

This prevents the use of ELEMS_13_ASC, though of course, the optimizer
may choose to let another table go natural. If so, do the same kind of
changes to those joins (adding the +0 to the part referring to ELEMS).

HTH,
Set