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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.