CODEWITHSUNDEEP
mysql
aRyhan
aRyhan

Reputation: 355

should i create index for each foreign key?

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'??

Upvotes: 1

Views: 3066

Answers (2)

Karl Kieninger
Karl Kieninger

Reputation: 9129

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

Upvotes: 3

holtc
holtc

Reputation: 1820

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

Upvotes: 1

Related Questions