MySQL Array/List Of Items And Performance

Question

I have a Users table. Every user can create one or more lists of movies.

So I searched a little bit and found an answer in Stack Overflow that the best way to create lists is to create A List Table With List_Id And User_ID Columns and a ListEntry Table With List_ID and Movie_ID Column.Then I retrieve list entries by running "SELECT Movie_ID FROM ListEntry WHERE List_ID=x"

My Question is this. When my site grows and I have over 1000 users where each one has 2-3 lists where every list has 50 movies, the ListEntry table may have over 100.000 Entries. Isn't that gonna slow down the database ? Is this the way most sites work ? any better way of doing this ?

100k entries is not so huge number for databases, but you should optimize your query, put the right indexes and narrow your search — 123dcck
– 123dcck, Commented Jul 1, 2013 at 11:06

Noam Rathaus · Accepted Answer · 2013-07-01 11:07:42Z

1

Assuming that you are NOT going to store IDs of movies as anything but int(11) or probably less than in(11) - I am pretty sure there is no need for THAT many movies :} - the table will be "big" in amount of rows, but not big in amount of data.

MySQL is very efficient at locating and sorting values that have a small key length, i.e. your Movie_ID and User_ID will be two small keys ( int(11) or similar ), so their JOIN will be also small and done fairly quickly.

Anyway, I wouldn't worry about this, I have tables that have more than 10^8 (100,000,000) records if they are properly indexed and have good keys, MySQL has no difficulty handling it.

answered Jul 1, 2013 at 11:07

Noam Rathaus

5,6082 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Strawberry · Accepted Answer · 2013-07-01 11:05:53Z

1

100,000 entries? MySQL isn't even going to feel it - and neither will you. Come back when you have in excess of 10 million.

answered Jul 1, 2013 at 11:05

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Comments

juergen d · Accepted Answer · 2013-07-01 11:06:19Z

1

Databases are designed to handle big loads of data. With proper indexing DBs can hold billions of records in a table.

answered Jul 1, 2013 at 11:06

juergen d

205k40 gold badges303 silver badges377 bronze badges

Comments

MKorsch · Accepted Answer · 2013-07-01 11:12:36Z

1

These number of rows inside a table won't be a problem for your MySql Server if the entries are well indexed. Some nice tips about the MySql performance can be found here. This is a german site with two (english) presentations about MySql performance.

answered Jul 1, 2013 at 11:12

MKorsch

3,8313 gold badges18 silver badges26 bronze badges

Comments

dijkstra · Accepted Answer · 2013-07-01 11:06:33Z

0

You can use mongoDB. It is schemaless. You can have list inside a document. (table)

answered Jul 1, 2013 at 11:06

dijkstra

1,0682 gold badges16 silver badges40 bronze badges

Collectives™ on Stack Overflow

MySQL Array/List Of Items And Performance

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related