CODEWITHSUNDEEP
mysqlsql
priya
priya

Reputation: 26699

Index gets created twice for a certain column

create table foo (id, name)
create table bar (id, foo_id, value, ...)

We created a foreign key relationship for foo_id referencing foo(id), which seems to be creating an index for the same column implicitly. We also have a requirement to create a compound index based on foo_id, value in bar.

Does it make sense to declare the indexes in the above mentioned fashion or is there a way to optimize index creation in the above case?

Upvotes: 1

Views: 213

Answers (1)

Tadeck
Tadeck

Reputation: 137360

It depends on how you use it. The indexes are to speedup the queries' executions and foreign keys are to make the content of your database consistent.

When it comes to foreign keys, here is a part of Foreign Key constraints documentation:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.

So everything is ok with the indexes created when the foreign keys are created.

Upvotes: 1

Related Questions