-1

I want to use foreign but I should add index.I heard that indexes is slow for inserting,this is really big issue because table's role which I need add foreign key , has a lot of update,insert,delete.Should I use foregn key?I dont want to slow down my query

Alex
  • 29
  • 5
  • i think you are over engineering,load test and see what is the performance,if it is not acceptable,then you can think of tuning.. – TheGameiswar Jul 11 '16 at 06:19

2 Answers2

0

let me explain what the actual whole concept of SQL Indexing and References

  • Indexing is same as normal indexing in our books when we are looking for particular page/content/topic rather than referring one by one page we see in index and figure out the address of it.
  • references is use to maintain the relationship between particular table or column
  • how SQL exactly working when you are performing any operation [excluding Insertion] they are first going to search those data. first it will check in index[if available] else searching for data one by one row.
  • so if you have an indexing you can make all the operation faster[ you have to make indexing of the those column on base you are performing most of operation like an ID,..] -in case of relationship you can also maintain in your code[only choose this option when you have an deep understanding of database and its flow]

Suggession must use indexing [on only one field] and yes use relation as per your need it will never going to down your performance

umeshkumar sohaliya
  • 267
  • 8
  • 24
  • But when I create table for examle posts table and there are post id (primary key) and also user_id and both of them should have Index because primary key itself is index and also I shoult=d use another index for user_id because it is foreign key.How can I use only one Index? – Alex Jul 11 '16 at 07:51
  • i mean one index per table and for foreign key you haven't need to apply indexing because its directly refer to primary key [ if you havent need of indexing on primary key you can also prevent them to be primary key] – umeshkumar sohaliya Jul 11 '16 at 08:10
  • But in my example I need primary key because post id should be autoincrement – Alex Jul 11 '16 at 10:40
  • do you make primary key just for auto increment? you can also make it auto increment without applying primary key. – umeshkumar sohaliya Jul 11 '16 at 14:48
  • Yes I know but if I do it posts id can be duplicate and primary key prevent it – Alex Jul 11 '16 at 17:18
  • But again if I use unique key it means that I have again 2 INDEX and also in this example post_id cant be null and it isnot nonclustered that why I should use primary key.How can I handle with it? – Alex Jul 12 '16 at 18:50
0

An index on a foreign key is used when:

  • parent row is about to be deleted,
  • parent's key (which is referenced by the foreign key) is about to be modified.

(It is not used when inserting or updating non-referenced fields of parent, nor when inserting, updating or deleting child.)

And of course, queries (especially JOINs) "on top" of foreign keys are quite common, and they may benefit from such indexes too.

So, as a rule of thumb, if you do the above operations often, consider having an index on the foreign key. And since every index requires maintenance, measure whether the price of maintenance justifies the speedup you get on these operations (as a rule of thumb, it usually does).

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Can I use a lot foregn key for different tables(for each of them only 2 index one of them foregn)?For example I have posts table and there are image_id and user_id which is depend on other tables Should I give them foreign key?(My post table has a lot of select,insert and few update ) – Alex Jul 11 '16 at 10:55
  • Yes. You should use FKs everywhere it is logical to do so. In fact, avoiding FKs for "performance" is usually a [very bad idea](http://stackoverflow.com/a/20873843/533120) - you'll typically end-up losing *both* integrity and performance. And for whether these FKs need to be indexed, see above. ;) – Branko Dimitrijevic Jul 11 '16 at 11:10