0

If we have implemented Index on Primary key column in Table1 and on Referenced column in Table2. But we don't have Foreign key on Referenced Column.

Can you please explain is this good to implement foreign key to utilize Index more efficiently

3 Answers 3

3

The foreign key won't help to use the index more efficiently. It's just meant to ensure that the column in Table2 won't contain any invalid values (i.e. values which do not appear in Table1). You do need the index to ensure the foreign key check performs adequately, otherwise it needs to scan the entire Table1 if you add or update a record in Table2.

0
3

I might be wrong, but I don't think that the MySQL optimizer takes foreign keys into consideration. An index, on the other hand, may be very useful as a support for a foreign key.

However, the main purpose of a foreign key is completely different than what you are asking for. The purpose is to keep the database consistent and maintain referential integrity. When deciding for or against foreign keys, I think this what you should be concidering.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Apr 13, 2018 at 23:26
1

Declaring a FOREIGN KEY causes a particular INDEX to be created (if it does not already exist).

An INDEX (whether explicitly created by you, or implicitly created by a FK) is likely to improve performance of a JOIN.

(There is no "more efficiently".)

2
  • Recently , database have explicitly Indexes on columns that can be FK. So should i implemented FK as well? May 1, 2018 at 5:23
  • Add an FK for integrity checks (not for performance). Adding the FK should notice that there is already an index and not add a duplicate index.
    – Rick James
    May 1, 2018 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.