Does creating a FK automatically index the column?

Question

I am using mySQL 5.1 and am wondering, is it necessary to add CREATE INDEX syntax on a column after you have defined it in a FK relationship. Common sense tells me that if a column is a key (foreign or otherwise), then it needs to be indexed - but you never know ...

Do I have to EXPLICITLY create an index on my FK - or does creating an FK implicitly creates an index on the column ?

Pascal MARTIN · Accepted Answer · 2009-12-20 16:10:39Z

7

Quoting this page of the MySQL manual : 13.6.4.4. FOREIGN KEY Constraints :

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.
In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.
Such an index is created on the referencing table automatically if it does not exist.

answered Dec 20, 2009 at 16:10

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bob Jarvis - Слава Україні Over a year ago

MySQL is unusual in this regard, although I wish other products would do the same.

Bill Karwin Over a year ago

+1 One interesting effect is that on MyISAM (which doesn't support foreign keys), declaring a foreign key still creates an index.

Bill Karwin Over a year ago

@Alex: Right, a foreign key does not have to be unique. If it did, you could never have a many-to-one relationship.

Collectives™ on Stack Overflow

Does creating a FK automatically index the column?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related