Subject Re: So confused
Author robert_difalco
> 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.

Right, sorry about that. I was pairing down the query to the point
where enames was still doing a natural. In the actual query, we are
ordering by Enames.displayName. However, adding or removing this order
by does not change the Natural for the join on Enames.

While your solution works (thanks!), I'm still not sure why the query
as written does not use the Enames primary key index?

> Also, you have pushed a JOIN criterion into the WHERE clause
> (see Elems.lastVersionID = EVers.ID).

Right, it was originally a Join, I forgot to put it back there when
typing the message while I was trying a few variations. Fwiw, on the
original query, it did not change the plan either way having it as a
JOIN or in the WHERE clause.

> That relationship between EVers and Elems looks suspiciously
> circular to me, as well...

I suppose it kind of is. The Elements table has a one to many
relationship with the Versions table. Each version has the ID of the
element it is a version of. This makes it fast to query a set of all
version for a given element. The element has a column for the identity
of it's "last version". This makes querying the current version of an
element very fast. The node is is the node that a set of elements
belongs to.

> 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?

Do you think we should consider switching to an intersection table for
the above scenario? Seems to work well as is. In this query we are
asking for all the "last versions" for the specified node. And yeah,
we are getting the expected output. As always thanks for your help.