1

I have table department that has two column (dept_ID and dept_name) my another table is login which i create a foreign key for the column dept_name referencing dept_name at table department. so i create an index named index_department, now in login table, i want to create another foreign key for the column eadd which will reference to DIFFERENT table named info_table.

should i create another index for the second foreign key??

another scenario, i want to create a dept_name column at info_table too. can i use the same index 'index_department'??

aRyhan
  • 315
  • 3
  • 14
  • 1
    Is there any reason why you reference `department.dept_name` instead of `department.id`? – N.B. Nov 25 '14 at 14:06
  • You create indexes to meet performance requirements for queries. What queries do you want to run that might use such an index? You can edit your question with examples. – Gordon Linoff Nov 25 '14 at 14:09
  • Foreign keys are always indexed. – S.Pols Nov 25 '14 at 14:16
  • i reference the name so that everytime i view the child table, the department name will appear not the ID shouldn't i use index for this small database? i really can't understand when to use index – aRyhan Nov 25 '14 at 14:18
  • So why aren't you asking what indexes are and when to use them, instead asking what to index and what to FK? Can you see how silly it is to give you detailed answers if you don't have the knowledge to understand it? If you just want a copy paste solution then you're at the wrong place and I've no clue what's the deal with female images and beginner questions, but this trend just seems like a huge insult to people on SO who help others. – N.B. Nov 25 '14 at 14:35

2 Answers2

3

The general answer is, "It depends."

As @gordon-Linoff commented "You create indexes to meet performance requirements for queries."

Indexes take up space and and take processing time as the have to be maintained. So the case for any given index depends on the trade off between cost and usage. For example if you the data rarely changes, but you look it up a lot you will prefer to have more indexes.

My educated guess is that on the scale you are probably working you do want the indexes on all your foreign keys.

Specifically in mysql you seem to get the index is you formally add the FK constraint. It is discussed here does mysql index foreign key columns automatically

I say formally, because you can have implied foreign key relationships without actually declaring/enforcing the constraints. People sometimes do that to avoid even the cost of the checking/enforcing constraint. The risk is in updates that violate referential integrity. But I'm drifting onto a tangent.

As a side note, there is some pertinent discussion here does a foreign key automatically create an index

Community
  • 1
  • 1
Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
0

In MySQL (at least 5.6, which I am using), indices are automatically created for foreign keys.

holtc
  • 1,780
  • 3
  • 16
  • 35