Post reply

Basic Index question

  • September 2, 2004 at 2:25 pm

    #102185

    Hi-

    Given I have a table which contains three foreign key ID's(Primary key int's) - what's the best way to approach indexing them?

    I know they will constantly be used to join to the other 3 tables.  Should I add one index containing all 3 ID's?  Or one index for each?  Uniqueness has nothing to do with this problem - the same row(s) can be entered multiple times.

    All 3 satellite do have plenty of rows, unique rows, in them so cardinality is not an issue either.

    Also, if I index all 3 as one index - is this considered a "Covering Index"?

    Thanks in advance - B

  • September 3, 2004 at 12:56 am

    #521587

    Hi,

    I would create 3 separate indexes. ORACLE uses indexes on FK columns in the child table while validating data, to avoid chld table locking. I don't know yet if MS SQL is so smart , but anyway, I think that indexing FK column is a good rule of thumb.

    A "covering index" is a term related to a specific query. If the query can be executed using index access only (without table access), that index is called a covering index for that query. A covering index for one SQL may not be covering for another SQL.

    I hope this helps.

    Regards,

    Goce.

  • September 3, 2004 at 9:18 am

    #521676

    I agree that 3 separate indexes would be best in most cases. An exception would be if you know for a fact that an overwhelming majority of queries that will be qualified by one column will also be qualified by another, in which case it would make sense to combine them in the same index.

  • September 3, 2004 at 10:35 am

    #521689

    Remember that you can have up to 249 non-clustered indexes on a table, and 1 clustered.

    Assuming that the Clustered is already taken, a separate index on all three of the columns is a good approach.

    The reason to make a covering index is if you have queries that could stand a boost in performance. Look at the where clasuses of your queries to see if there can be any benefit.

    Remember also that if you put an index on a table, the optimizer doesn't have to use it. If you are in doubt, put it on, then run some usual queries and use the Query analyzer to see if the new indexes were used. There is also the index tuning wizard as a possibility.

    It is best to remove unused indexes because they will incur greater overhead when dealing with updates and inserts.

    Go forth and index!

     

    Michael

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply