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?


1 Answer 1


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.

  • 1
    I'd give a +2 on this one, if I could.
    – Aaron
    Jan 18, 2012 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) Jan 18, 2012 at 15:29
  • 1
    I can't think of a scenario where it would be faster...
    – JNK
    Jan 18, 2012 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, 2012 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, 2012 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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