Database design relationship for movie

Hellow.I create a table of movie in mysql that have 1million movie for example with id and name column.I also create user table with id ,name columns and a comment table that have movie_id and user_id and comment columns to save all user comment.question is if i have 1million movie and if each movie have 1000 comments (lets say we have 1000 active user), then comment table should have 1,000,000,000 rows and it is so slow to use a query to find all comment on one specific movie. Any of you friends can help me with bether solution or way to do this?I appreciated so much

2 answers

  • answered 2020-01-22 06:33 Renzo

    If you have a billion of comments than a solution with a relational database require a billion of rows for the comments table.

    On the other hand this does not slow down queries if you design correctly the database: just add an index on the user_id and movie_id columns of the comments table, and the query that find all the comments for a certain film, or all the comments of a certain user will be answered very efficiently, without “scanning” all the table.

  • answered 2020-01-22 06:51 som

    If MySQL is the only choice you have to store the comments then based on your use case you can also look at partitioning the data for user comments.