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 = App\Robot::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 = App\Robot::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 = App\Robot::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('App\RobotMapPositionLog','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?

UPDATE

I have identified ->with('current_robot_position') to be the issue that is slowly down the entire query.

As some others have sugggested, I have made the foreign key column (robot_id) column used in the relationship an index column and also changed the orderBy with latest() but neither of this has helped reduce the load time significantly

Root Cause

The hasOne relationship is taking an unexpectedly long time to process, even with latest('id')/orderBy('id') applied. Although this setup is meant to return only the most recent entry, the query still scans the entire RobotMapPositionLog table, which contains hundreds of thousands of records, thus contributing to the long load times.

Solution

Simply by specifying limit(1) the load time dropped from 30seconds-1minute to it being loaded within a second

public function current_robot_position(){
    return $this->hasOne('App\RobotMapPositionLog','robot_id','id')
        ->orderBy('id','desc');
        ->latest('id')->limit(1);
}