Using Indexes in mysql correctly

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

score 1 · Accepted Answer · edited May 23 '17 at 12:21

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?

edited May 23 '17 at 12:21

Community

1
1

answered Apr 09 '13 at 07:21

Bryan Anthony Abrams

72
6

Thank but which field should be primary and which index in relational tables? – user969068 Apr 09 '13 at 07:22
2

I'm not going to answer that for you. You should read that post and understand how those things in sql work and then consider the relationship between your data and how you'll be searching for it. – Bryan Anthony Abrams Apr 09 '13 at 07:24
:) From what I understand I should use both as INDEX in relational tables, just wanted to be sure If that is correct way so asked above question. Thanks – user969068 Apr 09 '13 at 07:41
'Only one field can be the primary key on each table' - this is wrong, MySQL has composite keys. – 1615903 Apr 09 '13 at 07:44
if you make an index with table A and B, primary key is the unique key that identifies each record in table A. index key is the unique key for the table B. So index key is the key connection of A with B. – Nik Drosakis Apr 09 '13 at 07:44
@user1615903 that's a composite primary key than, not a primary key. "acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table". Also the word primary in itself means one, not multiple. – Bryan Anthony Abrams Apr 09 '13 at 09:09
@BryanAnthonyAbrams so you're saying that composite primary key is not a primary key? Also, the quote is correct, but in your answer you say that only one FIELD can be the primary key on each table, which isn't true because primary key can be a composition of two or more fields. I hope you understand my point here. – 1615903 Apr 09 '13 at 09:31
Sorry I had read the posts wrong, I corrected my post to adjust it. Regardless it's useful for the original author of this question to read the other question since it'll explain how he can build relations between tables. – Bryan Anthony Abrams Apr 09 '13 at 10:10

Using Indexes in mysql correctly

1 Answers1