A relation table is the common solution to representing a many-to-many (m:n) relationship.
In the simplest form, it combines foreign keys referencing the two relating tables to a new composite primary key:
A AtoB B ---- ---- ---- *id *Aid *id data *Bid data
How should it be indexed to provide optimal performance in every JOIN situation?
Aid ASC, Bid ASC
) (this is mandatory anyway, I guess)Bid ASC, Aid ASC
)Bid ASC
)I made some tests, and here is the update:
To cover all possible cases, you'll need to have:
CLUSTERED INDEX (a, b)
INDEX (b)
This will cover all JOIN
sutiations AND ORDER BY
Note that an index on B
is actually sorted on (B, A)
since it references clustered rows.
As long as your a
and b
tables have PRIMARY KEY
's on id's, you don't need to create additional indexes to handle ORDER BY ASC, DESC
.
See the entry in my blog for more details:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With