How to initiate and perform a DB action periodically in php/mysql

Question

Im using php/mysql and have to update a user's counter at a given frequency from their creation date. For category1 users, the counter increments after every 24 hours for 3 days, for category 2 after each week for 4 weeks..

I thought of creating a trigger on users table so that after each insertion I create an event that starts at the creation and has the desired frequency. But I don't know how the DB could react especially that several thousand of events will be created.

Other possibility is a cronjob that will check the creation date, category and the last occurence every hour for example. But this will not match the date/time of creation and also have to check every row to do the job when the update is not applied to some users.

which approch is best and won't impact the DB performance (very large number of users). Is there other efficient way to do this?

Maybe use crontab for it http://stackoverflow.com/questions/18737407/how-to-create-cron-job-using-php — Arman P., Jan 28 '17 at 01:04
Add a MySQL event. Checkout this answer http://stackoverflow.com/q/14805742/1542307 — Karan Shah, Jan 28 '17 at 01:42

score 0 · Answer 1 · answered Jan 28 '17 at 05:14

Wouldn't recommend adding a crontab entry for each user. Two options here MySQL events or using a queue like beanstalkd and then adding a job into the queue.

I'm more inclined towards MySQL events. Here is a doc:

http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

How to initiate and perform a DB action periodically in php/mysql

1 Answers1