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
Add a comment
|
3 Answers
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.
answered Apr 13, 2018 at 12:46
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.
answered Apr 13, 2018 at 12:49
-
Comments are not for extended discussion; this conversation has been moved to chat.– Paul White ♦Commented Apr 13, 2018 at 23:26
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".)
answered May 1, 2018 at 4:36
-
Recently , database have explicitly Indexes on columns that can be FK. So should i implemented FK as well? Commented 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. Commented May 1, 2018 at 15:31