I have a Laravel application where I’m retrieving data from two tables: LoginData and LocationStatus. The LoginData table contains records with a single IMEI, while the LocationStatus table contains multiple records for each IMEI, with a serverDatetime timestamp indicating when the record was created.

Here’s a simplified version of my code:

$offline_mhes = LoginData::select('loginData.id','imei','speed','ignition','loginData.batteryVoltage as batteryCurrent', 'loginData.extVoltage as extVoltageSupply','loginData.latitude','loginData.longitude','loginData.updated_at','batteryCurrent','GNSSStatus','bus.bus_name as object_name','loginData.integrate')
    ->join('bus', 'loginData.imei', '=', 'bus.imei_number')
    ->where('loginData.integrate','=','1')
    ->whereNotNull('imei')
    ->whereNotIn('imei',$active_mhes_past_hour)
    ->get();

foreach ($offline_mhes as $offline_mhe) {
    $latest_location = LocationStatus::where('imei',$offline_mhe->imei)
        ->orderByDesc('serverDatetime')
        ->first();
    // issue is here....
}

However, the LocationStatus table contains approximately 4 million records, and the query to retrieve the latest record for each IMEI is taking nearly 1 minute to execute, impacting performance.

I’m looking for suggestions on how to optimize this query to improve performance. I’ve already considered indexing the imei column in the LocationStatus table, but I’m open to other strategies or best practices to speed up this operation.