FKs with indexes...

  • Hi,

    I've read several articles stating that FKs should have indexes on both tables (primary and referenced) and it has quite logic since when deleting/updating on the referenced table the primary one will be searched for those records...

    I also read some stating that it's best to have few wider indexes rather than lot narrow one's due to index maintenance, size on disk, what happens when a record is updated/deleted, ....

    But it seems like these both statements are a bit contradictory since creating indexes on FKs will create lots of narrow indexes... for example I have a table that will have 30 "extra" narrow indexes, and since they are FKs no wide index will cover the narrow ones.

    Is it always advisable to create indexes on FKs or, since in this case, the records aren't that much deleted and the keys never changed, can we "skip" the FKs indexes on the primary tables?

    Thanks,

    Pedro



    If you need to work better, try working less...

  • It depends.

    There are always cases where "the rules" need to be broken.

    In a case like that, with 30+ FKs needed, that gets into the realms of both (a) you probably won't benefit much from 30+ indexes, and (b) what is going on in that database?!? 🙂

    But really, and seriously, you'd have to look at query patterns for the table and the related tables. Some might benefit from indexes. Maybe even all of them. But it will really depend on the queries that are in use.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • It's a products table... it has VAT, warranty, supplier, units, .... and many other references.

    The referenced tables don't have many records (top on has 20) but the products can have 16000 or many many more...

    And since the referenced tables can't be updated (code column is read only after insert) it can only be deleted what doesn't happen that much...

    Referenced tables with few records usually means that it isn't inserted/updated/deleted that regularly (in our case the tables are configured usually once and left alone) so narrow indexes on the primary table for these ones can be a rule to break... I guest..

    We also have some other cases that probably require the primary table index...

    In cases were the FK columns can't be updated cause they are read-only and the data isn't deleted (just a bit column with record state) the indexes don't make sense right?

    Thanks,

    Pedro



    If you need to work better, try working less...

  • I'd have to run some tests to say definitively whether indexes would help or not, but I can say I've seen similar systems and indexes weren't needed there.

    So, probably you're fine without.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks,

    Also if the indexes are necessary they'll probably show up on the missing index DMV query.... 🙂

    Pedro



    If you need to work better, try working less...

  • That's definitely possible.

    Or they'll show up in index plans when you're reviewing performance issues.

    Until it turns into something that needs fixing ...

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks,

    Better wait and see what shows on the sys.dm_db_missing_index_groups and/or the sys.dm_exec_query_plan

    Pedro



    If you need to work better, try working less...

Viewing 7 posts - 1 through 6 (of 6 total)

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