Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Optimizing Query Performance for Matching Records Between Two Tables with Large Data Sets

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.

Tags:

2

Answers


  1. 0

    Maybe you need to use a subquery instead to send each query to get LocationStatus.
    Look at this section of the Laravel documentation (link), maybe you find what you want.

    Login or Signup to reply.
  2. 0

    Assuming Laravel >= 10 and MySQL >= 8.0.14, you can use a lateral derived table to retrieve the latest location.

    $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', 'latest_location_status.*')
    ->join('bus', 'loginData.imei', '=', 'bus.imei_number')
    ->joinLateral(
        LocationStatus::whereColumn('imei', 'loginData.imei')
            ->orderByDesc('serverDatetime')
            ->limit(1),
        'latest_location_status'
    )
    ->where('loginData.integrate', '=', '1')
    ->whereNotNull('imei')
    ->whereNotIn('imei', $active_mhes_past_hour)
    ->get();

    Regardless of whether you switch to this method or continue with your separate queries, you need to add a composite index on (imei, serverDatetime).

    For older versions of Laravel, this answer shows how you can add macros for lateral derived tables.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search