Optimise database query to avoid memory issues

I have a huge Users table (maybe around 4M rows) and I want to run a monthly Job for checking their last login date and if they have not logged in update user and update isPassive column with true.

$users = \DB::table('users')
                   ->whereNull('isPassive')->get();
foreach($users as $user)
{
   if(!$user->wasActive())
   {
      $this_user = (new User)::where('id', $user->id)->first();
      $this_user->isPassive = true;
      $this_user->save();
   }
}

I have learned to run jobs with Laravel but this gives me memory issues. Well I can increase the server memory but from what I have read here, increasing the memory is not always the best solution.

I tried the chunk results but when I use where() it just hangs. If I use it without and conditions it works nice though.

I am wondering how would a experienced developer would break this job to use less memory?

Labelled it as Laravel but this is a question for any new programmer may face when learning better manage the memory.

Thanks for any advice

Solution

There's a few issues with your code. First:

$this_user = (new User)::where(...)->first();
...

This line is running a new query in every iteration of your foreach loop, so you're essentially running 4 million additional, unnecessary queries. That query is redundant, since you already have $user, and $this_user would be the same thing. Change your code as follows:

$users = User::whereNull('isPassive')->get();
foreach($users AS $user){
  if(!$user->wasActive()){
    $user->isPassive = true;
    $user->save();
  }
}

With that, you cut your queries in half.

Also, I'm not sure what wasActive() method does, but if you can move that logic onto your initial query, you can likely remove the loop, running a single update statement:

DB::table("users")
->whereNull("isPassive")
->where(...) // Apply logic from User wasActive() check
->update(["isPassive" => true]);

Hopefully that helps.