laravel - How to update large database in mysql database using cronJobs

Question

I am very thank full if anyone help me to resolve this issue.

Detail: I have a mysql table where 110000+ record stored with Unique application(column) number. Now there is Detail API which will take only 1 application number at a time in URI parameter and return details of that application number it will take Approx 1 min to respond for now.

Now i need to update that records(multiple column) using Cron Job Scheduling to always updated record in database. and it will progressively update.

Flow: Get application number from database -> Call detail API -> update there record on database.

Issue: there is large number of record in database so we can not call API for all application number in ones.

I am using Laravel 7 and Guzzle HTTP client 7.2 HTTP Client for API calling.

Any suggestion are welcome !

Update: (Tested) I am thinking to do something like this and agree with @bhucho comment to call cron in every 15 minute

We create one more column in table for last_updated_id default 1 and we will write a query to fetch application number something like and get 100 or 500 records in one slab using Laravel take methods from here Database Queries

$latestIdInColumn = myTableClassRef::max(last_updated_id);

$applications = myTableClassRef::select('application_number)->where('id', '>', $latestIdInColumn)
->take(100)
->get()->toArray();

Here we call detail API and update for each application number, when there is last application update done we will store that id in last_updated_id.

When again cron call we have that last_updated_id so we will apply same filter in next query where id '>' $latestIdInColumn. now we will get next 100/500 records where id is greater then $latestIdInColumn.

if ( $applications  )
{
  For all other available id
} else {
  when record not found.
  Here we will reset the last_updated_id = 1
}

Now function will fetch from id 1.

• It is not tested yet i just planing to do this, i am happy to have feedback on this.

Note: Now API not taking 1Min to respond I resolved this issue.

Update 2: It was working as expected the only issue with default value of column. means if it is 1 then we can't user > in query because its not consider id 1 initially if we use >= then every last record update 2 times i will resolve this but have an issue with now all other going good.

Answer 1

等待大神答复