Jul 10, 2022
0
Level 3
Query very slow in app but fast in console
I have the following query in a conroller:
'students' => Auth::user()->site
->students()
->leftJoin('student_meals as m', function ($join) {
$join->on('m.student_id', '=', 'students.id')
->where('date_served', Request::only( 'serviceDate') )
->where('meal_type_id', Request::only( 'mealType'));
})
->where('enter_date', '<=',Request::only( 'serviceDate') )
->where('exit_date', '>=',Request::only( 'serviceDate') )
->OrderByName()
->filter(Request::only('search', 'serviceDate', 'grade', 'hr'))
->select('students.id as studentId', 'first_name', 'last_name', 'grade', 'hr', 'students.sfa_id as sfaId', 'students.site_id as siteId', 'sisId', 'm.id as mealId', 'm.meal_type_id', 'm.created_at as created_at', 'void')
->get()
->map(fn ($students) => [
'studentId' => $students->studentId,
'name' => $students->name,
'grade' => $students->grade,
'hr' => $students->hr,
'sfaId' => $students->sfaId,
'siteId' => $students->siteId,
'sisId' => $students->sisId,
'mealId' => $students->mealId,
'mealType' => $students->meal_type_id,
'created' => $students->created_at,
'voided' => $students->void
]),
That in sql translates to:
SELECT `students`.`id` as `studentId`, `first_name`, `last_name`, `grade`, `hr`, `students`.`sfa_id` as `sfaId`, `students`.`site_id` as `siteId`, `sisId`, `m`.`id` as `mealId`, `m`.`meal_type_id`, `m`.`created_at` as `created_at`, `void`
FROM `students`
left join `student_meals` as `m` on `m`.`student_id` = `students`.`id` and `date_served` = '2022-08-15' and `meal_type_id` = '2'
WHERE `students`.`site_id` = 12 and `students`.`site_id` IS not NULL
and `enter_date` <= '2022-08-15' and `exit_date` >= '2022-08-15'
and `students`.`deleted_at` IS NULL
I have over 10 thousand records in 'students' table and over 1 million in 'student_meals'. When I run the query in the sql console it completes in about 37ms, in the app Clockwork shows it taking over 1 sec.
Can someone explain why?
Follow up question- would is 37ms for above query on that many records be considered good performance? Or should do I need to work on it more?
Please or to participate in this conversation.