I have about 10 tables in mySQL. 3 of them are for logging user's data and I am using GroceryCrud so admins can display each of these three tables. I was (am) concerned about the "little mySQL" database being hammered since it also has tables that make the whole application work. For this reason, besides keeping the logged data in mySQL, I am also logging it into DynamoDB which is an AWS noSQL database.
I would like to limit the amount of logged user data that i am keeping in mySQL to "the last 30 records". If user want the whole complete list they can download a CSV file from DynamaoDB. I am hoping to relieve the little mySQL database from getting totally overworked.
I have done some research and found ORDER BY and LIMIT and somehow want to use these to delete older records from mySQL (there will of course still remain a copy in DynamoDB).
When should I do this? I don't want to check for this every time a user sends in a message from a browser. It would slow the system down to a crawl. I also don't want ot do this when a user logs into the app because it will cause the app to appear sluggish. I was thinking perhaps nightly at midnight, I can run a small app that will clean up the database (ie keep only the last 30 records for each user). But how do I do this within the CI/PHP world? My app only "wakes up" when a user logs in (or a browser sends a message).
Someone mentioned running a CRON job. Can I write a CI/PHP app that gets kicked off by CRON?
proof that an old dog can learn new tricks
