Question on indexes (Multi-column vs. Multiple single column)

Let's say I have a table with a composite key of 4 foreign keys. I can access the table using 1 or more of the keys to limit and/or sort my data set. The question is, which is better for this: a single multi-column index or 4 indexes, one for each column?

Thanks in advance.

Using 4 primary keys is going to slow down that table to a crawl. You need to have a think about your design since there should be no real reason to have a 4 column primary key

It depends on how you query the table, hope this info helps you determine which is better for your situation:

With the composite index (col1,col2,col3,col4) then queries can only use the index if you have a where clause that partially matches the index from left to right.

1. [select ? from table where col1=? and col2=? and col3=?] will use the index

2. [select ? from table where col2=? col3=? col4=?] can't use the index.

If the index is split many things can happen. The most likely is that it will use the indexes it can and then merge the sub-results to determine the final set. This is an extra step that would not be necessary if the queries are all restricted by the entire index.

You don't say what DB, but some have an 'explain plan' capability so you can see the plan the optimizer will use, this can be quite revealing.

Just 1 final note, key!=index. A primary key or foreign key are actually constraints usually implemented as an index. But you can also have indexes that are purely for performance reasons