I've encountered a problem on a current task involving a simple n-to-m relation table.
In my table I have the following columns:
(a): FK to table_a
(b): FK to table_b
The usual approach would be to create a composite primary key on (a) & (b) giving me the ability to search efficient for:
(a)
(a) & (b)
But I also need to be able to search efficient for (b) alone. So in general this are my search scenarios:
(a)
(b)
(a) & (b)
Would it be better to create
1. a composite PK on (a) & (b) and another single index on (b)?
2. Or create two single indices on both (a) and (b) and accept the fact that the search for (a) & (b) might not be as fast as it could (because it need to look up two seperate indices instead of one)?
Also in the future there might be (c) so then I would need to search for (a), (b), (c) or any combination between.
Anyone who had that problem in the past and knows a reasonable solution for that?
Edit:
Additional requested information:
table_b will be a lot larger than table_a, possibly round 10-20 times larger.
Query percentages for the rows are expected to be around:
(a): 40%
(b): 40%
(a) & (b): 20%
You should create two indexes, one a composite index on (a, b) and the other either only on (b) or on b and more columns.
If you are creating two indexes anyway, you are already incurring the costs of the two indexes. Removing a column from an index is a small savings, relative to the overhead of maintaining the indexes. You might as well have the indexes that work best on your queries.