I have a model Post which has a expiry_date. I want to know what is the
best way to manage scalability in this case. 2 options:
1. Whenever I want to SELECT from the table, I need to include where
expiry_date > NOW. If the table Post grows like a monster, I will be in
trouble. Imagine after 3 years or more. Indexes will be huge too.
2. Have a trigger, cron job, or a plugin (if it exists) that would go
around the table and move expired items to a new table Post_Archive.
That way, I maintain only current Posts in my main table, which implies
that over 3 years I won't be as bad as option 1.
I have a model Post which has a expiry_date. I want to know what is the
best way to manage scalability in this case. 2 options:
1. Whenever I want to SELECT from the table, I need to include where
expiry_date > NOW. If the table Post grows like a monster, I will be in
trouble. Imagine after 3 years or more. Indexes will be huge too.
You could have a table with millions of rows and such a query would be
fast (as long of course as you had an index on the expiry_date
column). Not to say that moving expired posts somewhere else wouldn't
necessarily be a bad idea but certainly not something I would do
preemptively
1. Whenever I want to SELECT from the table, I need to include where
expiry_date > NOW. If the table Post grows like a monster, I will be in
trouble. Imagine after 3 years or more. Indexes will be huge too.
Obviously using indices is the easiest. I'm not sure how many posts
you're expecting, but unless you *know* it'll run into the millions with
a high percentage of cache misses this sounds like premature
optimization. First let your RDBMS deal with the size of the indices
(it'll do fine) and then let your DBA worry about it (he'll cache it
into RAM and put it on a fast disk).
1. Whenever I want to SELECT from the table, I need to include where
expiry_date > NOW. If the table Post grows like a monster, I will be in
trouble. Imagine after 3 years or more. Indexes will be huge too.
Obviously using indices is the easiest. I'm not sure how many posts
you're expecting, but unless you *know* it'll run into the millions with
a high percentage of cache misses this sounds like premature
optimization. First let your RDBMS deal with the size of the indices
(it'll do fine) and then let your DBA worry about it (he'll cache it
into RAM and put it on a fast disk).
Thank you guys. You saved me a lot of work. I will not move them out of
the table even though I know I am supposed to expect billions of rows if
everything goes as expected!