title: How to make JOINS faster?
body: <p>I had this query to start out with:</p>
  
  <pre><code>SELECT DISTINCT spentits.*
  FROM `spentits`
  WHERE (spentits.user_id IN
           (SELECT following_id
            FROM `follows`
            WHERE `follows`.`follower_id` = '44'
              AND `follows`.`accepted` = 1)
         OR spentits.user_id = '44')
  ORDER BY id DESC 
  LIMIT 15 OFFSET 0
  </code></pre>
  
  <p>This query takes <code>10ms</code> to execute. </p>
  
  <p>But once I add a simple join in:</p>
  
  <pre><code>SELECT DISTINCT spentits.*
  FROM `spentits`
  LEFT JOIN wishlist_items ON wishlist_items.user_id = 44 AND wishlist_items.spentit_id = spentits.id
  WHERE (spentits.user_id IN
           (SELECT following_id
            FROM `follows`
            WHERE `follows`.`follower_id` = '44'
              AND `follows`.`accepted` = 1)
         OR spentits.user_id = '44')
  ORDER BY id DESC 
  LIMIT 15 OFFSET 0
  </code></pre>
  
  <p>This execute time increased by <code>11x</code>. Now it takes around <code>120ms</code> to execute. What's interesting is that if I remove either the <code>LEFT JOIN</code> clause <strong>or</strong> the <code>ORDER BY id DESC</code> , the time goes back to <code>10ms</code>. </p>
  
  <p>I am new to databases so I don't understand this. Why is it that removing either one of these clauses speeds it up <code>11x</code> ? And how can I keep it as is but make it faster?</p>
  
  <p>I have indexes on <code>spentits.user_id</code>, <code>follows.follower_id</code>, <code>follows.accepted</code>, and on <code>primary ids</code> of each table. </p>
  
  <p><strong>EXPLAIN:</strong></p>
  
  <pre><code>1   PRIMARY spentits    index   index_spentits_on_user_id   PRIMARY 4   NULL    15 Using where; Using temporary
  1   PRIMARY wishlist_items  ref index_wishlist_items_on_user_id,index_wishlist_items_on_spentit_id  index_wishlist_items_on_spentit_id  5   spentit.spentits.id 1   Using where; Distinct
  2   SUBQUERY    follows index_merge index_follows_on_follower_id,index_follows_on_following_id,index_follows_on_accepted
  
  index_follows_on_follower_id,index_follows_on_accepted  5,2 NULL    566 Using intersect(index_follows_on_follower_id,index_follows_on_accepted); Using where
  </code></pre>