I need to store a list of items and that list belongs to a single user. The lists are going to be huge, so this has to be done as efficiently as possible. The lists also have to be modifiable to a small degree (able to add new items).

This is my (possible) solution:
Have a table items, where we store the data of our items. Table lists contains info about our lists (name, user_id etc). Table lists_lengths contains our lists lengths, e.g:

list_id | item_id_start | item_id_end
1       | 1             | 200
2       | 201           | 220
1       | 221           | 300

If the lists get a lot of modifications, this might end up being a poor design though. Is this good or is the blatant list_id in the items table just the way to go?

  • List 1 contains 1-200 and 221-300, correct? And no item shows up in two lists, correct?
    – Rick James
    Apr 7, 2015 at 21:57
  • How big is "huge?" Thousands, millions, billions? Apr 8, 2015 at 1:51
  • How's about you do it the standard way, test its performance, then tweak if necessary? Apr 8, 2015 at 7:41
  • Correct. Each list can contain up to 500,000 items. Each user is going to have 5-20 of them. There's enough users that optimization is something that I'm worried about.
    – Oliver
    Apr 8, 2015 at 7:46
  • What would the "standard" way be? list_id in items table? I might just go with that and restructure if issues arise.
    – Oliver
    Apr 8, 2015 at 8:06

1 Answer 1


Once the table gets really big, you will find that looking up an item to find out what list it is in becomes painfully slow.

I discuss that, plus provide efficient code, in my blog on ip-ranges. Your use case (non-overlapping ranges of items "owned" by various "lists") seems to be the same problem.

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.