Expired items

Hi guys,

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.

Any help? comments from your experience?

Thanks, Youssef

Hi guys,

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

Fred

Youyou Semsem wrote:

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).

Roderick van Domburg wrote:

Youyou Semsem wrote:

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).

-- Roderick van Domburg http://www.nedforce.com

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!

Thanks, Youssef