Level 24
What does your query look like?
Apr 16, 2019
9
Level 3
Laravel relation query taking too much time to load
Hello, I have two models, one for school Member and the other for school Attendance, each one with the corresponding database table.
My goal is to get all school members who have at least one attendance between some particular date, as an example let's take '2019-04-05' and '2019-04-12' (one week)
So in my Members model I have a relation:
public function attendance() {
return $this->hasMany('App\Attendance')
->whereBetween('scan_date', array('2019-04-05', '2019-04-12'));
}
The problem is that the attendance table has like 76000 records now so the query takes about 8 seconds to get the results. Is there a way to make this query faster? maybe limit in some way?
Level 13
Check with below query use the limit(),
->whereBetween('scan_date', array('2019-04-05', '2019-04-12')->limit(100)->get());
Level 3
$members = \App\Member::withoutGlobalScope(SchoolScope::class)
->whereNotNull('email')
->when($to_school, function ($query, $to_school) {
return $query->where('school_id', $this->communication->school_id);
})
->has('attendance')
->get();
select * from `members` where `email` is not null and `school_id` = ? and exists (select * from `attendances` where `members`.`id` = `attendances`.`member_id` and `scan_date` between ? and ? and `attendances`.`deleted_at` is null) and `members`.`deleted_at` is null
The relation query is the one that is taking long time ->has('attendance') without that, it works replay quickly
Level 3
@MUNAZZIL - This does the same, 8 seconds waiting... I even try to limit to 10 but no luck.
Level 13
->has('attendance') replace and use below one,
->with('attendance')
else
return $query->where('school_id', $this->communication->school_id)->with('attendance');
Level 24
Have you analyzed the query with EXPLAIN? Does the scan_date column have an index?
2 likes
Level 3
@MUNAZZIL - I'm sorry but this does not work, thank you anyway ;)
Level 3
@STAUDENMEIR - Seems that adding an index the query time is as expected, I'll post results here. Thank you
Level 53
staudenmeir is right, check your indexes on your database: Make sure that at least these fields have an index applied to them:
- member_id
- scan_date
- deleted_at (optional)
That should speed up your query a lot. if you don't have an index on scan_date, it's doing a complete table scan....
1 like
Please or to participate in this conversation.