如何加快我的cron作业/数据库更新速度

Question

I have a cron job that runs once every hour, to update a local database with hourly data from an API.

The database stores hourly data in rows, and the API returns 24 points of data, representing the past 24 hours.

Sometimes a data point is missed, so when I get the data back, I cant only update the latest hour - I also need to check if I have had this data previously, and fill in any gaps where gaps are found.

Everything is running and working, but the cron job takes at least 30 minutes to complete every time, and I wonder if there is any way to make this run better / faster / more efficiently?

My code does the following: (summary code for brevity!)

// loop through the 24 data points returned
for($i=0; $i<24; $i+=1) {

// check if the data is for today, because the past 24 hours data will include data from yesterday
if ($thisDate == $todaysDate) {

// check if data for this id and this time already exists
$query1 = "SELECT reference FROM mydatabase WHERE ((id='$id') AND (hour='$thisTime'))";

// if it doesnt exist, insert it
if ($datafound==0) {
$query2 = "INSERT INTO mydatabase (id,hour,data_01) VALUES ('$id','$thisTime','$thisData')";
}

}
}

And there are 1500 different IDs, so it does this 1500 times!

Is there any way I can speed up or optimise this code so it runs faster and more efficiently?

Answer 1

This does not seem very complex and it should run in few seconds. So my first guess without knowing your database is that you are missing an index on your database. So please check if there is an index on your id field. If your id field is not your unique key you should consider adding another index on 2 fields id and hour. If these aren't already there this should lead to a massive time save.

Another idea could be to retrieve all data for the last 24 hours in a single sql query, store the values in an array and do your checks if you already read that data only on your array.