Bad where performance in Laravel

Question

I want to apply a condition whereto a relation. That's what I'm doing:

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;

Runs after 70 seconds. If I manually rewrote the request as follows:

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

Runs after 0.4 seconds. Why where existsis this the default behavior if it is so slow? Is there a way to generate the correct query where inwith the query builder, or do I need to enter raw SQL? Maybe I'm generally doing something wrong?

replaysthe table has 4M rows, playershas 40M rows, all the corresponding columns are indexed, the data set does not fit into the memory of the MySQL server.

Update: it is established that the correct request can be generated as:

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

- , exists

Answer 1

, whereHas ,

mysql

https://dev.mysql.com/doc/refman/5.7/en/optimize-overview.html

php-

, larval

$replay = DB::select('select * from replays where id in (
select replay_id from players where battletag_name = ?) 
order by id asc limit 100', ['test']
);