title: Speed up Laravel Eloquent/MySQL query
body: <p>I have an Eloquent query that is currently taking about <strong>700ms</strong> to run and it will only increase as I add more websites to the user account.  I'm trying to see what the best way to optimize it is so that it can run faster.  </p>
  
  <p>I really don't want to save the "results" of my calculations and then just fetch those in a smaller query later because they could update at any moment and that would mean they would not be accurate 100% of the time. Although I am pretty sure that would speed up the query, I don't want to sacrifice accuracy over performance.</p>
  
  <p>This is essentially the raw query that runs: </p>
  
  <pre><code>select  *
      from  
        ( SELECT  `positions`.*,
                  @rank := IF(@group = keyword_id, @rank+1,
                                     1) as rank_e0686ae02a55b8ad75aec0c7aaec0a21,
                  @group := keyword_id as group_e0686ae02a55b8ad75aec0c7aaec0a21
              from  
                ( SELECT  @rank:=0, @group:=0 ) as vars,
              positions
              order by  `keyword_id` asc, `created_at` desc
        ) as positions
      where  `rank_e0686ae02a55b8ad75aec0c7aaec0a21` &lt;= '2'
        and  `positions`.`keyword_id` in ('hundreds of IDs listed here')
  </code></pre>
  
  <p>The query is generated using the solution <a href="https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/" rel="nofollow noreferrer">mentioned here</a> with regards to getting N number of relations per record.</p>
  
  <p>I've tried running a simpler query without the N number of relations per record, and it actually ends up being even slower because it's fetching much more data.  So the problem I think is that there are too many <code>ID</code>s that are trying to be matched up in the <code>IN</code> method of the query.</p>
  
  <p>In my controller I have:</p>
  
  <pre><code>$user = auth()-&gt;user();
  $websites = $user-&gt;websitesAndKeywords();
  </code></pre>
  
  <p>In my <code>User</code> model: </p>
  
  <pre><code>public function websitesAndKeywords() {
  
          $user = auth()-&gt;user();
          $websites = $user-&gt;websites()-&gt;orderBy('url')-&gt;get();
  
          $websites-&gt;load('keywords', 'keywords.latestPositions');
  
          return $websites;
  }
  </code></pre>
  
  <p>I would appreciate any help anyone could provide in helping me speed this thing up.</p>
  
  <p>EDIT: So I think I figured it out.  The problem is the <code>IN</code> clause that Laravel uses every time eager loading is used to load relations.  So I need to find a way to do a <code>JOIN</code> instead of eager loading.</p>
  
  <p>Essentially need to convert this:</p>
  
  <p><code>$websites-&gt;load('keywords', 'keywords.latestPositions');</code></p>
  
  <p>Into:</p>
  
  <pre><code>$websites-&gt;load(['keywords' =&gt; function($query)
  {
      $query-&gt;join('positions', 'keywords.id', '=', 'positions.keyword_id');
  }]);
  </code></pre>
  
  <p>That doesn't work, so I'm not sure what's the best way to do a <code>JOIN</code> on a current collection.  Ideally I would also only fetch the latest N positions too and not all data.</p>
  
  <p>Here are indexes on <code>positions</code> table:</p>
  
  <p><a href="https://i.stack.imgur.com/IIGQo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IIGQo.png" alt="enter image description here"></a></p>
  
  <p>And here is what explain returns for the query:</p>
  
  <p><a href="https://i.stack.imgur.com/tNi0u.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tNi0u.png" alt="enter image description here"></a></p>