Why the index was automatically created to foreign key fields?

Question

I create the foreign key to one field in my table, the index was automatically created to that field. why the index was automatically created to foreign key fields?

related http://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically — Haim Evgi, Jul 28 '11 at 10:09
Because indexes are automatically created for columns that act as foreign keys. Check MySQL manual. — Michael J.V., Jul 28 '11 at 10:16
Not a dupe, the linked question asks: does MySQL put an index on foreign keys (answer yes), this question aks: Why? — Johan, Jul 28 '11 at 10:18

score 1 · Answer 1 · answered Dec 17 '11 at 18:07

The indices are created on foreign keys to improve performance. If you have a foreign key is common to want to get related items, an index allows you to quickly get these items. Also the conditions imposed on foreign keys (delete, update) take advantage of the index to work fast. Finally you need to perform integrity checks when creating foreign keys, this requires performing searches, these searches take advantage of the indexes.

score 0 · Answer 2 · answered Jul 28 '11 at 10:16

0

Foreign Key fields link to the contents of fields in other tables.

If we create a table like so:

Table Patient
  id 
  name
  address

And a table Illness

Table Illness
  id
  patient_id foreign key to patient.id
  description

MySQL checks to see if a foreign link to table patient actually matches up with the id in Illness. It also does the reverve, if a patient is deleted, it checks to make sure it is not referenced in illness. In order to do this effiently it needs to index these fields, otherwise it will have to do a spend too much time doing full table lookups.

Besides, the word key is a synonym for index so it makes sense to index keys :-).

answered Jul 28 '11 at 10:16

Johan

74,508
24
191
319

k..Then how can i create foreign keys without create index? – Sunitha Bharathy Jul 28 '11 at 10:39
@Sunitha, that's impossible why would you want to do a thing like that? – Johan Jul 28 '11 at 10:52
Like Johan said, "In order to do this efficiently it needs to index these fields, otherwise it will have to do a spend too much time doing full table lookups." – ryanprayogo Dec 15 '11 at 19:09

Why the index was automatically created to foreign key fields?

2 Answers2