how to store item lists - separated by comma or by space

[cammac]

I have currently favorite movies stored in a comma separated list: (Terminator, Rocky, Die Hard)

 

Then I noticed flick r which is a heavy traffic site is using space seprated lists with double quotes used for combining multi-word items: (Terminator Rocky "Die Hard")

 

That made me start wondering which is the best method for storing in and searching from MySQL?

[Wildbug]

A common mistake of new MySQL users is to store multiple bits of data as delimited strings in a single column.  Usually the disadvantages of this method outway any perceived convenience.  This also introduces problems such as having to write extra routines to remove items and items containing the delimiter themselves (a movie such as "Run, Lola, Run!" contains commas).

 

The standard way to do something like this would be to create a new table for movies.  It need only contain two columns, a user id and movie title.  You could still introduce delimiters when retreiving the data -- GROUP_CONCAT() has a SEPARATOR option.

 

Maybe you still want to use a single column, but at least be aware that it will eventually be a pain.

[cammac]

When I designed it into one comma separated field my thinking was:

 

When the user edits his favorite movies in a text area where they appear as comma separated in a textarea to the user... when he has 20 movies there and deletes 5 from the existing list and adds 2, then if we say a database has 20 million users and each has separate fields for all movies, lets say 100 million rows, then the script would have to go through 100 million rows to find all 20 of the user's movies and compare those all with the new list of movie titles submitted,

then delete 5 rows and add 2 rows (7 write operations), whereas when all are in 1 field, it would be only 1 write (UPDATE) operation and mysql could stop searching as soon as it has found the 1st row with matching username, instead of having to find 20 more rows belonging to the user.

 

Am I mistaken in my thinking that my method would be better for a very high traffic site?

[Barand]

and mysql could stop searching as soon as it has found the 1st row with matching username,

 

Sounds like your user "Aaron" gets a good response while poor "Zack" may as well get a night's sleep while you search for his.

 

Have you considered indexing the table?

[cammac]

yes,  I have 2 fields userId, interests - with index on userId

 

Assuming one would use the "all movies in one field" method, would there be any difference between using a comma separated list to using a space+quotes separated list in terms of performance when mysql searches through these lists for various users.

[fenway]

Update such "lists" is annoying at best.