正确使用mysql中的索引

Question

I would like to know that how can I make a proper Indexed table, to understand this concept I will be using movies as example:

I have these 5 tables with multiple fields but I will list here these tables primary indexes fields only:

movies
  movie_id = primary index
actors
  actor_id = primary index
geners
  gener_id = primary index
reviews
  review_id = primary index

And then I have these tables for relation with 2 columns each, I am unsure what type of indexes should have these relational tables:

movie_actor
   movie_id,actor_id
movie_gener
   movie_id,gener_id
movie_review
   movie_id,review_id

I have join on these fields If I wants to get one movie details I will use such query:

SELECT * 
FROM movies as m
LEFT JOIN movie_actor  AS ma  ON ma.movie_id = m.movie_id
LEFT JOIN actors       AS a   ON a.ator_id   = ma.actor_id
LEFT JOIN movie_gener  AS mg  ON mg.movie_id = m.movie_id
LEFT JOIN geners       AS g   ON g.gener_id  = mg.gener_id
LEFT JOIN movie_review AS mr  ON mr.movie_id = m.movie_id
LEFT JOIN reviews      AS r   ON r.review_id = mr.review_id 
WHERE m.movie_id = 1234

So what kind of index should I use on relational tables (movie_actor,movie_gener,movie_review) both fields, primary on which one or just index on both? Thanks

Answer 1

Only one field can be the primary key on each table Your table can only have 1 primary key (corrected by user). Look at this post for information Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?