MySQL Question
Long running console command slows down
Initial text
I created console in Laravel project. The command takes html data from one table, checks two pattern matches for each record by preg_match. If it returns true, updates are being done to other table's record that has the same attribute as record from the first table that is currently in focus in foreach loop.
Number of records is cca 3500
After cca 150 iterations, command dramatically slows down, and I need one day for getting the command done.
I read all similar issues from this forum but they didn't help me. Not even the answer about forcing garbage collection.
Code is like following:
$ras = RecordsA::all();
$pattern = '/===this is the pattern===/';
foreach($ras as $ra){
$html = $ra->html;
$rb = RecordB::where("url", $ra->url)->first();
$rb->phone = preg_match($pattern, $html, $matches) ? $matches[1] : $rb->phone;
$rb->save();
}
I was searching for possible issue about preg_match performance but it was unsuccessful.
Did anybody meet such problem?
For MMMTroy update
I forgot to say I also tried custom but similar to your code:
$counter = DB::select("select count(*) as count from records_a")->first();
//Pattern for Wiktor Stribiżew :)
$pattern = '/Telefon:([^<])+</';
for($i = 0; $i < $counter->count; $i+=150){
$ras = RecordsA::limit(150)->offset($i);
foreach($ras as $ra){
$html = $ra->html;
$rb = RecordB::where("url", $ra->url)->first();
$rb->phone = preg_match($pattern, $html, $matches) ? $matches[1] : $rb->phone;
$rb->save();
}
}
Answer Source
"Pagination via OFFSET" is Order(N*N). You would be better off with Order(N), so "remember where you left off".