Poor whereHas performance in Laravel

Question

I want to apply a where condition to relation. Here's what I do:

Replay::whereHas('players', function ($query) {     $query->where('battletag_name', 'test'); })->limit(100); 

It generates the following query:

select * from `replays`  where exists (     select * from `players`      where `replays`.`id` = `players`.`replay_id`        and `battletag_name` = 'test')  order by `id` asc  limit 100; 

Which executes in 70 seconds. If I manually rewrite query like this:

select * from `replays`  where id in (     select replay_id from `players`      where `battletag_name` = 'test')  order by `id` asc  limit 100; 

It executes in 0.4 seconds. Why where exists is the default behavior if it's so slow? Is there a way to generate the correct where in query with query builder or do I need to inject raw SQL? Maybe I'm doing something wrong altogether?

replays table has 4M rows, players has 40M rows, all relevant columns are indexed, dataset doesn't fit into MySQL server memory.

Update: found that the correct query can be generated as:

Replay::whereIn('id', function ($query) {     $query->select('replay_id')->from('players')->where('battletag_name', 'test'); })->limit(100); 

Still have a question why exists performs so poorly and why it is the default behavior

Answer 1

Try this:

• mpyw/eloquent-has-by-non-dependent-subquery: Convert has() and whereHas() constraints to non-dependent subqueries.
• mpyw/eloquent-has-by-join: Convert has() and whereHas() constraints to join() ones for single-result relations.

Replay::hasByNonDependentSubquery('players', function ($query) {     $query->where('battletag_name', 'test'); })->limit(100); 

That's all. Happy Eloquent Life!