I needed to run a cron job that would allow me to split up my database queries and lessen the load on my host’s processor. My MySQL queries were taking 2 – 3 minutes to run.
Step 1: Make sure the column that your query is based on is indexed. An index is used to quickly find rows with specific column values. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs and the more processor your query consumes. If you’re running a large query based on a date (delete from your_table where DATE > ….), you want to index the date field. You can easily create an index in the phpMyAdmin interface (by clicking the lightning bolt icon for the specific column name) or if it’s a new field you’re adding, you can run a short MySQL command: CREATE INDEX new_index ON your_table(your_data)
Step 2: Here’s where the fun begins. I decided to divide up my long list of database inserts and deletes and break them up into 7 days. I then created a new file with the following code that I created, which I called MasterCron.php:
That simply calls a different php page (cron0.php, cron1.php, etc..) based on the current day of the week, with a unique set of database instructions for that day. Now you simply setup a new cron job calling MasterCron.php and let it do all the work.
The obvious alternative is to create 7 different cron jobs but if you have even 100 websites, you will likely rather see only 100 cron jobs in your list than 700. This just makes life a little easier.