469,958 Members | 1,988 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,958 developers. It's quick & easy.

Performance with combined key in junction table

I have an application with two tables, A and B. Each has an autonumber
unique ID field, plus other data.

I have a junction table, AB, containing fields AID, BID, and Count (a
number).

AB has a combined unique key comprising AID and BID, that is, any
combination of the two must be unique, even though any given AID or BID may
exist in many records.

I need to do queries across AB from A to B and vice versa.

Would they be faster if AID and BID were separate keys, and I handled
uniqueness constraints myself?

Jet 4.0, OLEDB, C++, Windows.

RDeW
Nov 13 '05 #1
1 1548
On Fri, 5 Aug 2005 20:14:42 -0700, "Riley DeWiley"
<ri***********@gmail.com> wrote:

Try it, and report back to us.

The PK in the junction table across both fields is the right thing to
do, so leave it.
I'm assuming you already created enforced relationships between the
tables. That creates hidden indexes behind the scenes, so you already
have at least an index on BID. Performance should already be optimum.

With compound indexes (those over more than one field), an additional
index over Field1 doesn't add value: the compound index is already
organized that way. In your case an index on Field2 is already taken
care of by your relationship to table B.

-Tom.
I have an application with two tables, A and B. Each has an autonumber
unique ID field, plus other data.

I have a junction table, AB, containing fields AID, BID, and Count (a
number).

AB has a combined unique key comprising AID and BID, that is, any
combination of the two must be unique, even though any given AID or BID may
exist in many records.

I need to do queries across AB from A to B and vice versa.

Would they be faster if AID and BID were separate keys, and I handled
uniqueness constraints myself?

Jet 4.0, OLEDB, C++, Windows.

RDeW


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by mksql | last post: by
8 posts views Thread by Marcy | last post: by
1 post views Thread by LurfysMa | last post: by
2 posts views Thread by Henry Stockbridge | last post: by
reply views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.