Move expired items?
I have a Post model that has expiry_date. I want to know what is the best way to manage scalability in this case. 2 options:
-
Whenever I want to SELECT from the table I need to include expiry_date> NOW in there. If the Mail desk grows like a monster, I'll be in trouble. Imagine in 3 years or more. The indices are huge too.
-
You have a trigger, cron job, or plugin (if it exists) that will go around the table and move obsolete items to the new Post_Archive table. Thus, I only support current positions in my main table, which implies that within 3 years I will not be as bad as option 1.
a source to share
If you need to archive data on a permanent basis (your # 2) then MaatKit is a good option.
It can “bite off” data in chunks, rather than launch bulk queries that consume a lot of resources (and avoid polluting your key cache).
So yes, you ran a Maatkit job from cron.
In the meantime, if you want to do # 1 at the same time, perhaps you can implement a view that conveniently completes the WHERE expiry_dat> NOW condition, so you don't have to include all of this in your code.
a source to share
The cron job sounds good to me, and it can be done by feeding a simple script directly to the command mysql
, like roughly:
CREATE TEMPORARY TABLE Moving
SELECT * FROM Post WHERE expiry > NOW();
INSERT INTO Post_Archive
SELECT * FROM Moving;
DELETE FROM Post
WHERE id IN (SELECT id FROM Moving);
DROP TEMPORARY TABLE Moving;
a source to share