Geek Answers Handbook

Should I use indexes for many-to-many data tables?

it makes sense to create indexes for a table named user_movies with the following columns:

user_id movie_id

This table will have much more information than an insert or update, but I'm not sure what to do. Also: is it enough to omit the primary key in this situation?

+5
source share
4 answers

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;

Note that the "primary key" is a constraint, not a column. It is best to have a primary key constraint in each table. Do not confuse the primary key constraint with an automatically generated pseudo-column.

MySQL . , .

+8

, .

+1

, BOTH-, (user_id + movie_id), (movie_id + user_id). ( , 10-20%) .

, , movie_id user_id ( , , , ).

+1

If you use such a “join table”, you will probably use some joins in your queries, and they will probably find the index for each of these two columns useful (which means two separate indexes).

0
source

More articles:


All Articles