检索MySQL表时克服PHP内存耗尽或执行时错误

Question

I have a big table in my MySQL database. I want to go over one of it's column and pass it in a function to see if it exist in another table and if not create it there.

However, I always face either a memory exhausted or execution time error.

//Get my table
$records = DB::($table)->get();

//Check to see if it's fit my condition
foreach($records as $record){
    Check_for_criteria($record['columnB']);
}

However, when I do that, I get a memory exhausted error.

So I tried with a for statement

//Get min and max id
$min = \DB::table($table)->min('id');
$max = \DB::table($table)->max('id');

//for loop to avoid memory problem
for($i = $min; $i<=$max; $i++){
    $record = \DB::table($table)->where('id',$i)->first();

    //To convert in array for the purpose of the check_for_criteria function
    $record= get_object_vars($record);
    Check_for_criteria($record['columnB']);   
}

But going this way, I got a maximum execution time error.

FYI the check_for_criteria function is something like

check_for_criteria($record){
    $user = User::where('record', $record)->first();
    if(is_null($user)){
        $nuser = new User;
        $nuser->number = $record;
        $nuser->save();
    }
}

I know I could ini_set('memory_limit', -1); but I would rather find a way to limit my memory usage in some way or at least spreading it some way.

Should I run these operations in background when traffic is low? Any other suggestion?

Answer 1

I solved my problem by limiting my request to distinct values in ColumnB.

//Get my table
$records = DB::($table)->distinct()->select('ColumnB')->get();

//Check to see if it's fit my condition
foreach($records as $record){
    Check_for_criteria($record['columnB']);
}