title: Poor whereHas performance in Laravel
body: <p>I want to apply a <code>where</code> condition to relation. Here's what I do:</p>
  
  <pre><code>Replay::whereHas('players', function ($query) {
      $query-&gt;where('battletag_name', 'test');
  })-&gt;limit(100);
  </code></pre>
  
  <p>It generates the following query:</p>
  
  <pre><code>select * from `replays` 
  where exists (
      select * from `players` 
      where `replays`.`id` = `players`.`replay_id` 
        and `battletag_name` = 'test') 
  order by `id` asc 
  limit 100;
  </code></pre>
  
  <p>Which executes in 70 seconds. If I manually rewrite query like this:</p>
  
  <pre><code>select * from `replays` 
  where id in (
      select replay_id from `players` 
      where `battletag_name` = 'test') 
  order by `id` asc 
  limit 100;
  </code></pre>
  
  <p>It executes in 0.4 seconds. Why <code>where exists</code> is the default behavior if it's so slow? Is there a way to generate the correct <code>where in</code> query with query builder or do I need to inject raw SQL? Maybe I'm doing something wrong altogether?</p>
  
  <p><code>replays</code> table has 4M rows, <code>players</code> has 40M rows, all relevant columns are indexed, dataset doesn't fit into MySQL server memory.</p>
  
  <p><strong>Update:</strong> found that the correct query can be generated as:</p>
  
  <pre><code>Replay::whereIn('id', function ($query) {
      $query-&gt;select('replay_id')-&gt;from('players')-&gt;where('battletag_name', 'test');
  })-&gt;limit(100);
  </code></pre>
  
  <p>Still have a question why <code>exists</code> performs so poorly and why it is the default behavior</p>