I have a video on demand style site that hosts series of videos under different modules. However with the way I have designed the database it is proving to be very slow. I have asked this question before and someone suggested indexing, but i cannot seem to get my head around it. But I would like someone to help with the structure of the database here to see if it can be improved. The core table is Videos:
ID bigint(20) (primary key, auto-increment)
pID text
airdate text
title text
subject mediumtext
url mediumtext
mID int(11)
vID int(11)
sID int(11)
pID is a unique 5 digit string to each video that is a shorthand identifier. Airdate is the TS, (stored in text format, right there maybe I should change that to TIMESTAMP AUTO UPDATE), title is self explanatory, subject is self explanatory, url is the hard link on the site to the video, mID is joined to another table for the module title, vID is joined to another table for the language of the video, (english, russian, etc) and sID is the summary for the module, a paragraph stored in an external database.
The slowest part of the website is the logging part of it. I store the data in another table called 'Hits':
id mediumint(10) (primary key, auto-increment)
progID text
ts int(10)
Again, here (this was all made a while ago) but my Timestamp (ts) is an INT instead of ON UPDATE CURRENT TIMESTAMP, which I guess it should be. However This table is now 47,492 rows long and the script that I wrote to process it is very very slow, so slow in fact that it times out. A row is added to this table each time a user clicks 'Play' on the website and then so the progID is the same as the pID, and it logs the php time() timestamp in ts.
Basically I load the entire database of 'Hits' into an array and count the hits in each day using the TS column. I am guessing (i'm quite slow at all this, but I had no idea this would happen when I built the thing) that this is possibly the worst way to go about this.
So my questions are as follows:
Is there a better way of structuring the 'Videos' table, is so, what do you suggest?
Is there a better way of structuring 'hits', if so, please help/tell me!
Or is it the fact that my tables are fine and the PHP coding is crappy?
