Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

I have user, items and user_items tables, and as usual user has many items. Tables contains: User: ~50K Items: ~3.5M UserItems: ~5M

I am using MySQL and most of my queries are selecting items of a user. So I wonder If it helps to keep data on UserItems table as follows,

user_id item_ids 1 1,22,23,45,66...

Then after getting ids, simply parse to get item ids. Any suggestion/experience?

share|improve this question

1 Answer

up vote 10 down vote accepted

This is a bad idea.

Putting more than one data element into a single field invariably leads to headaches.

You will be much better off making a new table to store this data, like:

Table UserItems

  • UserID int
  • ItemId int

And then one row per item per user. You can query this as needed and update it without any issues.

Think about adding one additional item to your proposed structure - it would be a challenge since you need to concatenate another space and another id to the end of the existing string.

It will also be a pain to check for a single item in that structure since you need to do a string search for every record.

share|improve this answer
1  
I'd give a +2 on this one, if I could. – BryceAtNetwork23 Jan 18 '12 at 15:01
Yes I know there will be some headaches with this approach, but I thought maybe I can make it a bit faster since I am interested only a small portion of much larger space (i.e 40 items in 5M items) – tackleberry Jan 18 '12 at 15:29
1  
I can't think of a scenario where it would be faster... – JNK♦ Jan 18 '12 at 15:30
1  
Yes, you are wrong :) Int comparisons are orders of magnitude faster than string searches. SQL is optimized for JOIN and relational operations, not string functions. Your 1 record in 125k will be a lot more expensive to check than looking for 40 records in 5m, especially if those 5m rows are all int fields which will take up less space than the equivalent strings. – JNK♦ Jan 18 '12 at 15:40
1  
Then you definitely should normalize the data as I suggest. I can't conceive of a scenario where it's more efficient to parse a string to get a list of ids to get data from than to just do two JOIN operations. – JNK♦ Jan 18 '12 at 18:11
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.