Marnen Laibow-Koser wrote: > Mike P. wrote: >> Hello, >> >> I've decided to use multiple tables for an entity (e.g. "todo_items1," >> "todo_items2," "todo_items3," etc.), instead of just one main table >> which could end up having a lot of rows (e.g. just "todo_items"). I'm >> doing this to try and to avoid a potential future performance drop that >> could come with having too many rows in one table. > > Robert already said it, and I agree: DO NOT DO THIS. Any decent DB > system is designed to handle tables in the millions of rows without > breathing hard. Just index your tables properly and make sure your > queries are efficient. > > If you have to use your DB's sharding features at some point, go for it. > But don't even think of doing this in the app layer. > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > [email protected]
Hi Marnen, Thank you also for your post and advice. It actually made me think more about this. So you're saying that even if I had millions of records in a table, and was noticing a decrease in performance, that I shouldn't split up the tables this way? Would a shard be the only option? One of the things that I keep coming back to when I think about the single-table option is that one of the fields will be rather large, like a blog-post in size. And I plan on indexing that to make it searchable. Also, there's a lot of insertions and deletions. Does it still make sense to include all of that in a single table? Or does that change any of your advice? I agree that this is an application layer split, sort of; but I don't think that this particular split is a bad thing. If I did this in pure Ruby, for example, I'm guessing that the effect would be minimal (just one class/method that looks up the correct table, and returns the object for that table). What's (potentially) making the app layer bloated is all of the unused 'has_many' attributes since I'm trying to stick with Rails. I really do want to be able to take advantage of the nice Rails methods and functionality... I'm just trying to avoid having a bunch of unused associations for each user object. Is there maybe a way to unload a 'has_many' association for an object? Or perhaps call 'has_many' inside of a user-specific method or something? Like if @current_user.todo_table_number == 1 has_many :todo_items1 elsif @current_user.todo_table_number == 2 has_many :todo_items2 elsif ... Anyway, the reason why I'm trying to stay away from the database-layer stuff is because they seem to be a bit 'much' right now, and also can apparently cause problems. For example, even adding foreign keys at the database-layer can evidently cause issues with testing, and sometimes they get lost in db:push/db:pull calls. Okay, how about this. Let's say that instead of my original post, I posted saying that I have a large table with 7 million+ rows that keeps growing. I had already split up the tables in the manner mentioned in the original post. Also, let's say that I really wanted to stick with the-Rails-way and was opposed to sharding. What would your advice be then? Thanks again for your help, Mike -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
