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?
2
Answers
To improve the speed of your Laravel query, try these steps:
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 theid
column, which I presume is the primary key ofrobot_maps
, but your Eloquent queries are using thename
column. Even ifname
is indexed, your numeric primary key can be queried faster than a string.robot_maps.name
column, or queryrobots
using a numeric primary key likeid
robots.serial_number
is indexed, or makeserial_number
the primary key if it’s an integer or UUID. Querying by numeric primary key still preferred here.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.