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

Question

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?

Answer 1

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.

Answer 2

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