CODEWITHSUNDEEP
sql-server
kacalapy
kacalapy

Reputation: 10134

should you index a many-to-many table?

should you index a many to many table such as user_role where it has 2 fields (userId and RoleId) ?

and what kind of index would be best?

Upvotes: 1

Views: 869

Answers (3)

richard
richard

Reputation: 12498

It depends. If there are a lot of user/role combinations (tens of thousands or more), and you are querying this information a lot, or need it to be particularly performant, then yes.

If there are not very many records, or you don't care if it's very performant, or don't use it very often, then most likely no.

For the usage part, SQL Server can help you decide if this is used enough to warrant an index. See here for some guidance.

Also, as Martin pointed out, if you have defined user/role as a primary key on this table, you already have an index, the clustered index, for that combination of columns.

Upvotes: 2

JNK
JNK

Reputation: 65147

As a rule, if you join on a field it's a good idea to have an index on it.

Your mileage may vary depending on table size, selectivity of the field (i.e. how many distinct values it has) and your ratio of updates/inserts to selects/seeks.

Upvotes: 0

Dustin Laine
Dustin Laine

Reputation: 38503

Indexes will help query a table faster, it really has nothing to do with the relationship itself. Are you queries slow? If so, then adding an index would help that. It will add some overhead to the insert and update statements.

Upvotes: 0

Related Questions