Blog

How To: Use MySQL Events

MySQL has a relatively new feature very similar to a crontab job (Unix/Linux) or task scheduler (Windows) for scheduling and executing tasks. Ever since the release of MySQL version 5.1.6 MySQL “Events” or “temporal triggers” have been included. So what are they? Well  you can now say: "I want the MySQL server to execute this SQL statement every day at 8:30am, until the end of the year" or anything similar that involves any number of SQL statements, and a schedule. How great is that.

Here's a simple example:

CREATE EVENT
e                               /* Event name */
ON SCHEDULE
EVERY 1 WEEK                    /* Interval */
DO
INSERT INTO t VALUES (0);       /* SQL statement */

The MySQL event scheduler is a thread that runs in the background looking for events to execute. It spends a lot of time sleeping — and won't do anything unless the new global variable "event_scheduler" is set to ON (1). So if you want to take advantage of this feature, do the following:

SET GLOBAL event_scheduler = 1;

To turn the feature off, do:

SET GLOBAL event_scheduler = 0;

I find MySQL Events extremely useful for creating and maintaining 'cache' tables with data integrity for very specific time intervals. This technique avoids having to query the large log tables to find data state at a specific time and date.

For instance, let's say you are administering a forum database and your client, the forum owner, has informed you that they want to begin monitoring a specific attribute of their forum and they have provided you with 3 monitoring time intervals; initialDate, midDate, finalDate. At these date and time intervals they want to know how many registered users last post date was on that day. Well, you could always query against the large 'post' table, however this forum is too large, and that query will bring the database to a crawl. So, a great option is to create a new table and use MySQL Events. You will need to create an 'event' with each date that will run the same query. Very easy.

Now, your client wants to further analyze their forum and has asked to be able to choose dates at their own will. Most solutions to the initial problem would entail your involvement each time your client wants to add a new date in this second problem. Well, by using Events and php, if you create an html form for your client to choose the group of dates and then utilize php and MySQL's integration, your form can create events on the fly for your client, satisfying their needs. Very easy again.
 

Tags: events, mysql