How to optimize mysql table for executing faster

Question

I made a mysql database for my project. I need to know if what I did is correct or not.

I made a master table to store all my movie details and I built some other tables to store the views and ratings. Am I doing wrong by using different tables to store views and ratings, although I can use the master table to store views and rating in each post.

If I add the rating and views fields to the master table itself, will it affect my database? What's the optimal solution?

I am using Myisam as lots of select queries are needed instead of insert.

Answer 1

If you are just using the fields for incremental value, you can use the master table. But if you wish to save extra parameters to a vote or view, you should use a secundary table. e.g. You want to save which user voted which value on which date. Than you should add a table 'votes' with the fields: voteId, userId, rating, date.

Afterwards you can use the AVG function to calculate the avarage vote.

Answer 2

thanx for ur response, i use the extra table for storing all the data so i want to know if every time i calculate the total and add it to or i mean update this in master table then it will be easy for me to show the top rated and top viewed post by calling only one table so could you tell me asumption is good or bad.