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


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.

| improve this answer | |

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.

| improve this answer | |
  • Thanks Glorfindel, as per my understanding. I should introduce Foreign key in whole database. – Shilpa Garg Apr 14 '18 at 5:09

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".)

| improve this answer | |
  • Recently , database have explicitly Indexes on columns that can be FK. So should i implemented FK as well? – Shilpa Garg May 1 '18 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 '18 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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