Question about indexes

[Puiu]

Hello,

I have a table with 3 foreign keys in it.

My question was: should I create a single covering index on the 3 FK or I should create 3 separate indexes ?

 

Thanks

[Nerseus]

It depends on how the index will be used. An index won't get picked unless the first column(s) are used in a query. Suppose you put columns A, B and C in an index like so:

CREATE INDEX idxTest ON TestTable(A, B, C)

 

A query using column B or C (or both) will not use this index. Indexes are a tricky business to figure out. If the table does not get a lot of INSERTs, then you could consider adding multiple indexes (one on each column) and one or more multi-column indexes if needed.

 

Now, if you always join to this table on all 3 columns, then it would help to put all 3 in the index.

 

-ner

[bri189a]

don't carry mutliple fields over as a single foreign key. Implement a surrogate key in the parent and bring that over as the foreign key.