DB table receiving too much data - another solution required

Question

I have a website where people can add their favorite series. There is one function that allows you to check the episodes that you saw.

Each episode that is marked creates one entry in the database table (with user_id, show_id and episode_id).
This table now exceeds 600,000 rows and is growing very fast!

I have indexes installed, but I feel that the performance of querying this table is getting worse and worse.

My thoughts on a new solution:

So, instead of:

user_id | show_id | episode_id  
1 ....... 123 ......7675  
1 ....... 123 ......7676   
1 ....... 123 ......7677  
1 ....... 456 ......5678  
1 ....... 456 ......5679  
1 ....... 456 ......5680  

I could do this:

user_id | show_id | episode_ids  
1 ....... 123 ......7675,7676,7677  
1 ....... 456 ......5678,5679,5680

Then I would have to split the string into an array and use array.include? (some-id) .
This should make the database easier, but for Ruby there will be a much heavier array.

? - ?

Answer 1

, ​​ . , varchar, -, .

, . :

• , .
• .

600 . ( ). . ram . , .

1 ( ), , , , .

Answer 2

, . , , , . , , . :

• ? - (, -, ..)?
• ? , .
• ? , , , . , , .
• ?
• mysql. .
• , - ( , ..)
• ,
• , . , .
• , " " / (nosql). 600k , .

, , - , . , ​​ , .

Answer 3

:

USERS
userid INTEGER PRIMARY KEY 
username text/varchar/whatever

SHOWS
showid INTEGER PK
showname   varchar or nvarchar or text  [depending on what database I was using]
etc etc


EPISODES
episodeid INTEGER PK
showid    INTEGER  FK references SHOWS   [index this field]
ordinal   DECIMAL   [indicates which episode  -- DECIMAL makes it easier to insert later an episode you overlooked] 
episodename text/varchar/nvarchar whatever   
etc etc

SEENIT
id  INTEGER AUTOINCREMENT  PK
userid  INTEGER    foreign key ref USERS
episodeid  INTEGER foreign key ref EPISODES

(userid, episodeid) , userid, . , .

Answer 4

I would stick to normalized data. This is more like a query optimization problem. Keep in mind that mysql (assuming you use it) uses only one index per query, and you can get better performance by tuning the composite index. Also use the EXPLAIN statement in the mysql query browser. More details here: http://dev.mysql.com/doc/refman/5.1/en/explain.html