Subject Re: So confused
Author Svein Erling Tysvær
--- In, Helen Borrie wrote:
> > 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
> >(
> > Elems.lastVersionID = EVers.ID AND
> > Elems.nodeID = -9223372036853775797
> >)
> >I get this:
> >

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


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:
> 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
> e.nodeID = ? /* -9223372036853775797 */

This is sound advice from Helen, as always.

> That relationship between EVers and Elems looks suspiciously
> circular to me, as 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):


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).