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

Laravel Eloquent Query Running So SLOWLY as compared to mySQL counterpart

Why is this laravel eloquent query running so SLOWLY?

I have a query running in a Laravel job that executes very slowly and inconsistently. Sometimes it takes 1-2 minutes to fetch the result, while at other times it completes in just 1-2 seconds for the same exact query.

Slow Full Eloquent Query ( Takes 1-2 minutes for the query to be completed )

$relevantRobot = AppRobot::where('serial_number', 'TEST-ID')
                    ->whereHas('robot_maps', function($query) use ($robot_map_name) {
                        $query->where('name', $robot_map_name);
                    })
                    ->with(['robot_maps' => function($query) use ($robot_map_name) {
                            $query->where('name', $robot_map_name);
                        },
                        'current_robot_position',
                        'current_robot_position.robot_map',
                        'latest_robot_deployment_information_request'
                    ])
                    ->first(); // Get the raw SQL query

Slow Reduced Eloquent Query ( Takes 1-2 minutes for the query to be completed )

$relevantRobot = AppRobot::where('serial_number', 'TEST-ID')
                    ->whereHas('robot_maps', function($query) use ($robot_map_name) {
                        $query->where('name', $robot_map_name);
                    })
                    ->with(
                        'current_robot_position',
                    ])
                    ->first(); // Get the raw SQL query

Fast Reduced Eloquent Query ( Completes in less than a second )

$relevantRobot = AppRobot::where('serial_number', 'TEST-ID')
                    ->whereHas('robot_maps', function($query) use ($robot_map_name) {
                        $query->where('name', $robot_map_name);
                    })
                    ->with(
                        'latest_robot_deployment_information_request',
                    ])
                    ->first(); // Get the raw SQL query

SQL Query ( Completes in less than a second )

select * from `robots` where `serial_number` = 'TEST-ID' and exists (select * from `robot_maps` where `robots`.`id` = `robot_maps`.`robot_id` and `name` = 'test' and `active` = 1);

Eloquent Relationship

 public function current_robot_position(){
        return $this->hasOne('AppRobotMapPositionLog','robot_id','id')
            ->orderBy('id','desc');
    }

Attempted Solution

After noticing the slow load time when eagerly loading current_robot_position, I added indexes to the columns used in that relationship (id). However, this hasn’t improved the performance.

I also tried converting the Eloquent query to a raw MySQL query using toSql(), and it ran extremely fast (under 1 second).

What is wrong? What am I missing?

Tags:

2

Answers


  1. 0

    To improve the speed of your Laravel query, try these steps:

    1. Optimize orderBy in Relationships: Use latest(‘id’) instead of
    orderBy('id', 'desc'):
public function current_robot_position() {
    return $this->hasOne('AppRobotMapPositionLog', 'robot_id', 'id')->latest('id');
}
    1. Add Indexes: Ensure that the robot_id columns in the robot_maps and robot_map_position_logs tables are indexed.
    2. Use limit(1): Limit the main query to one record directly in SQL:
    ->limit(1)->first();
    1. Reduce Nested Relationship Loading: Load nested relationships in two steps to avoid extra queries:
    if ($relevantRobot) {
    $relevantRobot->load(['current_robot_position', 'current_robot_position.robot_map']);
}
    1. Use Caching: Cache results for frequently accessed queries to prevent re-running the query:
    Cache::remember('relevantRobot-' . $robot_map_name, now()->addMinutes(10), function() { ... });
    Login or Signup to reply.
  2. 0

    I think your problem comes down to having a lot of data structured in a way that isn’t properly optimized for your query.

    I see in your raw SQL query your robot_maps subquery is searching on the id column, which I presume is the primary key of robot_maps, but your Eloquent queries are using the name column. Even if name is indexed, your numeric primary key can be queried faster than a string.

    1. Index robot_maps.name column, or query robots using a numeric primary key like id
    2. Make sure robots.serial_number is indexed, or make serial_number the primary key if it’s an integer or UUID. Querying by numeric primary key still preferred here.
    3. Make sure robot_map_position_log.robot_id column is indexed (guessing the table name based on model name)

    You could try lazy loading the relationships to help isolate which part of your query is the slowest.

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

Back To Top
Search