Storing a large number of objects that belong to a list

Question

I am using rails and have the following scenario: Users have_many lists and each list contains a number of words with each word having its own definition. The list show view shows all the words paginated by multiples of 30. I am concerned b/c a list can grow to have over 4,000 words and this seems like it would be way to expensive for the database to paginate if the list needs to be ordered alphabetically. I'm wondering what the fastest way to do this is. Maybe adding an index on the word?

I have considered saving a string in the list that contains all the words in the list separated by a space. I could then do a split(" ") on this string and use pagination on this array, but then I need to use regular expressions to add and delete words from this list along with a word object save.

I have also considered some sort of key-value store like tokyo cabinet. It looks like a B-Tree index could work.

Answer 1

Index it! This is by far the best option, and like Mark Thomas said, it'll give you any 30 consecutive words just as quickly as it'll give you the first 30. Just throw in an :order and a :limit and you're good to go - or, better yet, let a plug-in like will_paginate do it all for you.

The word string is a pretty messy approach - you'd have to rebuild the string every time you added or removed words, and work lookups would have to use slow comparison functions like LIKE or REGEX.

Tokyo (now Kyoto) Cabinet might not be versatile enough for what you need - it claims to be a key -> value store, and you might need multiple keys -> value, which I'm not sure it supports. Besides, why add another DB when you've already got one?

tl;dr INDEX!

Hope this helps!