Mike P. wrote: > 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?
Correct, unless there is no better way to improve things (which will almost certainly not be the case). > Would a shard be the only option? Depends on the DB. > > 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? That does not change any of my advice -- right up until the point where you have actual, measurable problems. And by then, your data model may be different anyway. Don't prematurely optimize, because you may be optimzing the wrong things. > > I agree that this is an application layer split, sort of; but I don't > think that this particular split is a bad thing. > On what basis do you think that? (I think you're wrong, but I want to know your reasoning.) > 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). But it is still inappropriate, because this can be handled more efficiently on the DB side than on the app side. > 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. > Then don't try to use a silly schema like the one you proposed. Perhaps you will ultimately need something like MySQL's MERGE table, but you don't need to worry about that yet. > 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 ... > Don't bother. > > 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, Right! They are a bit much right now -- because they are premature optimization. When you need them, they will be the right tools. > 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. You apparently are passing on FUD you've heard somewhere. I assure you -- from experience -- that this is completely untrue. > > 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? I would advise you to get over your anti-sharding bias and do things as I outlined above. > > Thanks again for your help, > Mike Best, -- Marnen Laibow-Koser http://www.marnen.org [email protected] -- 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.
