CODEWITHSUNDEEP
mysqlsqljoindatabase-designdatabase-performance
Rahul KR
Rahul KR

Reputation: 11

Mysql Table Structure Working Fast?

I am planning to create a website similar to IMDB.com. To reduce execution time I am using the following structure. Is it okay for faster working?

Table - 1

Id Movie_name description

1  name one    some description
2  name two    some description
3  name three  some description

Table 2

id actorname
1  name 1
2  name 2
3  name 3
4  name 4

Table 3

id movieid actorid

1   1       1
2   1       2
3   1       3
4   1       9
5   2       6
6   2       5
7   2       8
8   2       1

When I want to list actors in a movie program will retrieve actors ids from table 3 and find respective names from table 2 (using single query). When I want to list the movies of a actor it will retrieve movie ids from table 3 and find respective names from first table. Will it work properly? Any other ideas?

Upvotes: 1

Views: 96

Answers (2)

John Woo
John Woo

Reputation: 263733

This will give all actors in a specified movie,

SELECT  c.ID, c.actorName
FROM    table1 a
        INNER JOIN table3 b
            ON a.ID = b.movieID
        INNER JOIN table2 c
            ON b.actorid = c.ID
WHERE   a.ID = 1

This one will give all movies for a specified actor

SELECT  a.*
FROM    table1 a
        INNER JOIN table3 b
            ON a.ID = b.movieID
        INNER JOIN table2 c
            ON b.actorid = c.ID
WHERE   c.ID = 1

To further gain more knowledge about joins, kindly visit the link below:

UPDATE 1

This is called Relational Division

SELECT  a.ID, a.Movie_Name
FROM    table1 a
        INNER JOIN table3 b
            ON a.ID = b.movieID
        INNER JOIN table2 c
            ON b.actorid = c.ID
WHERE   c.ID IN (1, 2, 3)
GROUP   BY a.ID, a.Movie_Name
HAVING  COUNT(DISTINCT c.ID) = 3

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

I suggest that you modify table3 by taking away the id field. Use the movieid and actorid together as your primary key. You might want to add other fields to this table such as name of character and order of appearance as suggested in the comment by Jermaine Xu.

Upvotes: 0

Related Questions