CODEWITHSUNDEEP
mysqldatabaseindexing
psaniko
psaniko

Reputation: 1250

Should I use indexes for a many-to-many database table?

does it make sense to create indexes for a table called user_movies with the following columns:

user_id movie_id

There will be much more reading than inserting or updating on this table but I'm not sure what to do. Also: Is it adequate to omit a primary key in this situation?

Upvotes: 5

Views: 2229

Answers (4)

barry
barry

Reputation: 11

I have always heard that you should create a unique index on BOTH columns, first one way (user_id + movie_id) then the other way (movie_id + user_id). It DOES work slightly faster (not much, about 10-20%) in my application with some quick and dirty testing.

It also makes sure you can't have two rows that tie the same movie_id to the same user_id (which could be good, but perhaps not always).

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562260

The correct definition for this table is as follows:

CREATE TABLE user_movies (
  user_id INT NOT NULL,
  movie_id INT NOT NULL,
  PRIMARY KEY (user_id, movie_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
) ENGINE=InnoDb;

Notice "primary key" is a constraint, not a column. It's best practice to have a primary key constraint in every table. Do not confuse primary key constraint with an auto-generated pseudokey column.

In MySQL, declaring a foreign key or a primary key implicitly creates an index. Yes, these are beneficial.

Upvotes: 10

Pascal MARTIN
Pascal MARTIN

Reputation: 400932

If you are using such a "join-table", you'll probably use some joins in your queries -- and those will probably benefit from an index on each one of those two columns (which means two separate indexes).

Upvotes: 1

Daniel A. White
Daniel A. White

Reputation: 190905

I would index both columns separately and yes you can eliminate the primary key.

Upvotes: 2

Related Questions