Up to 1 million rows Performance + Design Help
Posted by: Tom Riddle
Date: February 05, 2009 05:24PM

Im trying to make a movie database and there should be up to a million rows, i mainly want it to be as fast as possible. Im not so good at designing a good database as i am just using the knowledge i got from online mysql tutorials and a few books, so i need help to make a fast scalable model.

There needs to be a table that holds all the data for the movie.At the moment i have it like this:

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),
moviename VARCHAR(200) NOT NULL,
urlmovie VARCHAR(200) NOT NULL,KEY urlmovie (urlmovie),
movieposter VARCHAR(200) NOT NULL, - (url to movie poster)
releasedate DATE NOT NULL,
ratings INT NOT NULL default '0', - (amount of user votes)
ratingtotal INT NOT NULL default '0', - (sum of ratings)
rating INT(2) NOT NULL default '0', - (current rating e.g 60)
genre VARCHAR(200) NOT NULL,
plot TEXT NOT NULL,
dvd INT(1) NOT NULL default '0',
vhs INT(1) NOT NULL default '0',
bluray INT(1) NOT NULL default '0',
numberofformats int NOT NULL,
formatdetails TEXT NOT NULL,
pictures TEXT NOT NULL,
clips TEXT NOT NULL,
worldsales VARCHAR(200) NOT NULL,
sequels TEXT NOT NULL,
addbyuser INT NOT NULL,
producer VARCHAR(200) NOT NULL);

Thats some of the main fields i need help with.
The "urlmovie" is set as a index as that will be how the movie will be identified... for example if the url is www.movie.com/spiderman-2 "spiderman-2" will be the urlmovie.

The first problem i face is about the other fields like "producer" and "genre".. i also want to be able to for example retrieve all movies with a genre of horror or a certain producer and im not so sure if it will be fast or slow with a huge amount of rows... im not sure if im doing it right...

The other problem is formats, basically at the moment the only way i can think to do this is to have the "numberofformats" column dispaly the number of formats the movie is aavailable in, if its larger than 1 then the script shows the "formatdetails" text which describes the differences between formats....
And the dvd/vhs/bluray colums all habe either a 0 or 1 in them depending on whether the movie is available in each format and that way i can search for movies available in dvd etc ...

but the problem is that there is alot fo different formats and i may have to add more as time goes on and then i will have to add the new format columns... so this way is not scalable... for example i add a million rows and then a few months later learnt hat there needs to be a "imax" format as well i will need to edit all 1 million rows....

The other problem i face is pictures field... basically each movie may have up to 30 scene pictures... and the only way i know how to display it would be to have the picture urls in the column seperated by a + or something and then to have the php script split the urls and display them.. but im not so sure if this will work fast.. as each page request the script will have to fetch the data, and split it then display it.... THis is also how the videos colums works , esxcept that it points to the video urls...
For example this field would look like:
http://www.youtube.com/?v=2323232+http://www.youtube.com/?v=2d3232+http://www.youtube.com/?v=23df454+http://www.youtube.com/?v=zdfef352

This is also how the sequels field and the related movies field looks like, for exmaple:
spiderman2+spiderman3+spiderman1+spiderman4

the script then seperates the names of the movies and searches through the database for each movie and displays the movie poster and the release date and includes a link to the movie page...

Im just not sure if this will be fast.... if there is a million rows.. and alot of visitors coming to each page...i dunno if there is a better way to design this.... this will work but each page load the script will have to use explode functions to separate pictures/ video urls etc and on the main page only display the first 6 pictures etc...

Any help would be appreciated, im still learning and im not sure how to do this right..
Thanks

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.