Best way to store Article body?

Rails
1

Dear Rails developers,

What is the best way to store an Article.body ?

Usually I put it just inside the articles table. But this proves to be
hogging the DB when I try to do a find(:all), or doing the articles in a
Category.find(… :include => :articles).

  1. Using find :select seems to be one way.
  2. Moving the Article.body to separate tables is another way. (like
    BLOBs)
    But unlike for BLOBs/images, I don’t really like this approach to be
    used on
    Article’s body.
  3. Moving the Article.body to filesystem?

Some way or another, the body will be used also for searching purposes.
Simple SQL [I]LIKE might good for now… though I’ll be moving to Ferret
very soon (having used it and satisfied).

Thank you.

–
Hendy I.
Web: http://hendy.gauldong.net
Mobile: +62 856 24889899
Yahoo Messenger: ceefour666
LinkedIn: http://www.linkedin.com/in/ceefour

2

Usually I put it just inside the articles table. But this proves to be
hogging the DB when I try to do a find(:all), or doing the articles in a
Category.find(… :include => :articles).

How big are the articles?

  1. Using find :select seems to be one way.

This could work but has some downsides. Any fields not included in
the select will act like they do not exist. @article.body will throw
a NoMethodError if it was not included in the select. Also, you can’t
use :include and :select together. The include overrides the select.
So if you need joins you have to do those manually too. I would
consider this option if you only want to use it on one or two list
type pages.

  1. Moving the Article.body to separate tables is another way. (like BLOBs)
    But unlike for BLOBs/images, I don’t really like this approach to be used on
    Article’s body.

Sounds like a good option to me. What don’t you like about it.
ActiveRecord associations make access easy and the body does not load
until you reference it.

  1. Moving the Article.body to filesystem?

That sounds like a lot of work for no real benefit over the separate
table approach.

Aaron

3

On 2/22/07, Aaron [email protected] wrote:

Usually I put it just inside the articles table. But this proves to be
hogging the DB when I try to do a find(:all), or doing the articles in a
Category.find(… :include => :articles).

How big are the articles?

Not so big perhaps. But 100 articles 100 KB each will cost 10 MB, not
very
pretty if most of them are wasted (i.e. the body isn’t displayed, maybe
just
an excerpt).

The problem is mostly because not everything that Model.find requests
(in
SELECT) is going to be used by controller nor displayed by the view.
This is
more of a commonality rather than exception. (the exception is, when we
have
to display the whole thing, and this only happens on ‘show’ action on
most
cases)

  1. Using find :select seems to be one way.

This could work but has some downsides. Any fields not included in
the select will act like they do not exist. @article.body will throw
a NoMethodError if it was not included in the select. Also, you can’t
use :include and :select together. The include overrides the select.
So if you need joins you have to do those manually too. I would
consider this option if you only want to use it on one or two list
type pages.

Yep, you’re right. I’ve heard :select was just invented in Rails 1.2?
(is
that true? wow)

If I had been able to visualize the whole app into my brain then it’d be
easier to decide between :select or blob table… (so I know what all
controllers do and what fields they may way) but the app is evolving. I
guess so does the DB.

I think it might be a good bargain to start with :select first (since
this
saves the number of migrations, tables, associations) … then when
things
get bad, just make a migration for that, to switch to separate tables?
(I’m
thinking this way for now)

  1. Moving the Article.body to separate tables is another way. (like BLOBs)

But unlike for BLOBs/images, I don’t really like this approach to be
used on
Article’s body.

Sounds like a good option to me. What don’t you like about it.
ActiveRecord associations make access easy and the body does not load
until you reference it.

Yes I agree with you it’s the easiest way to do it without any hacks.
(and
opens up possibilities of acts_as_* family of plugins)

  1. Moving the Article.body to filesystem?

That sounds like a lot of work for no real benefit over the separate
table approach.

Aaron

Thank you Aaron.

I “hate” separate tables maybe from the conceptual side… I think it
clutters the DB unnecessarily. There is really some tradeoff we have to
do
between clean design, practicality/productiveness, and performance. :frowning:
[damn, too many tradeoffs]

I love the :select way because on the DB design side, it’s “better”.
I’ve
had used the separate tables approach back in the days of MySQL MyISAM
(fixed for users table, containing no VARCHAR; and dynamic for users_ex
table, containing BLOBs and stuff)… and I didn’t like it. Some simple
details is that I can have NOT NULL… It’s pretty weird to specify that
an
association should have at least one child row?

–
Hendy I.
Web: http://hendy.gauldong.net
Mobile: +62 856 24889899
Yahoo Messenger: ceefour666
LinkedIn: http://www.linkedin.com/in/ceefour