Run a script based on timestamp entered in MySQL database

Question

Hi

I have a requirement to run a server script on scheduled times entered by the user. This is for a program for scheduling emails. A user can enter the specific times when they want to send the mail. I have a table which stores the userid, the email text and the time at which the mail is to be sent. This time is defined in minutes. I don't want to do it through a cron job as it would mean running cron jobs every minute. Is there a way to do this that is more machine efficient - maybe through database triggers or something. Any help is appreciated.

Thanks

Vinayak

Answer 1

If all your cron job is doing is a quick query to check if any mail is scheduled to send, then it's not going to be an expensive operation to run each minute. It should be less load on your server than a typical page load (which usually requires quite a few queries, and more).

Use a script run by a cron job, but make sure that it is as lightweight as possible. From what you describe, you should be able to get it down to one simple query that - most of the time - returns zero results. That's fairly lightweight.

Answer 2

I'm not that familiar with cron but when the user enters the info, I think you might be able to set a cron job that will run at that specific time. That way, you won't have to constantly run a general cron job. It will only run at all the specific times.

Answer 3

As drewm says, you will have to use a cron. Triggers will definitely put a much bigger load on your server than a simple cron job. Besides, mysql does not have any built in mechanism for sending emails so you will need to write a UDF to that. ASFAIK that UDF will have to be written in C. And unless you want to link to create an SMTP client from scratch you will have to link SMTP libraries to the UDF, all of this can get rather messy.

Sending emails with a cron job and a shell script or a php script is less than 10 lines of code.