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 wont 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]

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