Is a multi-column index generally worth it if the second column will always only have a few entries long for each entry in the first column?

Question

If I have two columns in a table, and I plan to do lots of queries on those two columns (which would normally suggest creating a multi-column index), but I also know that each unique value in the first column will only have around 3 to 5 values in the second column, is the multi-column index still worth it vs. just having an index on the first column? Assume I don't care about the cost of creating the index, I'm strictly interested in the extent to which the multi-column index will increase the query speed over an index for just the first column.

Answer 1

The issue is whether the multi-column index can cover the query or at least the where clause (assuming your queries are referring to filtering in the where clause).

In general, the answer is yes. Consider data that looks like this:

x     y       datapage
a     1       datapage_1
a     2       datapage_2
a     3       datapage_3
b     1       datapage_4
. . . 

If your query is:

select x, y
from t
where x = 'a' and y = 2;

Then, with no index, the database has to scan all data pages to find matching rows.

With an index only on x, the database can find all the "a" values, but it still has to load three data pages to get the y values.

With an index on both x and y, the database can directly go to "datapage_2".