Philip Borkholder wrote on 2015-06-26:
> Hello all,
>
> I know that Rushmore tech comes into play when you JOIN tables using
> fields that have index tags in their respective tables.
>
> However, does the tag have to match the name of the actual field being
> indexed? Reason I ask, in an app I support, performance was slow when
> joining two tables together on one key field.
>
> Let's say the field is:
> FGITEM
> The index tag is just ITEM
>
> The Join statement had:
>
> SELECT tableA.*, tableB.descrip FROM tableA INNER JOIN tableB on
> tableA.FGITEM = tableB.ITEM
>
> Once I added a new index TAG to tableA
> FGITEM tag FGITEM
>
> The query ran much faster.
>
> Does this make sense or am I misunderstanding it?
>
> Thank you, Philip Borkholder Vicksburg, MI
Philip,
The join condition needs to match the index.
In an old system the child table has a compound key.
parentTable (pkey n(5)...)
index on pkey tag pkey
childTable (fkey n(5), seq n(2)...)
index on padr(fkey,5) + padr(seq,2) tag pkey
select parentTable.*, childTable.detail ;
from parentTable left join childTable ;
on padr(fkey,5) + padr(seq,2) = padr(pkey,5)
Since parentTable is the primary, it is usually limited in the where clause.
The join is geared use the index on the larger side to limit.
HTH,
Tracy
Tracy Pearson
PowerChurch Software
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
