CODEWITHSUNDEEP
mysqlindexing
tstenner
tstenner

Reputation: 10281

What indexes should I use with MySQL for a m:n-relationship?

I have two tables, a and b, and want to create a m:n-relationship between the columns.
Naturally, I create a third table ab with the columns a_id and b_id.

Since all pairs should be unique, I create a primary index over both fields and one btree-index for each field. Now I have 3 indexes over a table with just 2 fields and think this is overkill. Are indexes for both columns actually needed or does MySQL handle cases like this in a special way?

Upvotes: 2

Views: 132

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

Your primary index over (a_id, b_id) will also cover searches on just a_id, so it is unnecessary to index a_id separately.

Upvotes: 3

Related Questions