How to make JOINS faster?

Question

I had this query to start out with:

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

This query takes 10ms to execute.

But once I add a simple join in:

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

This execute time increased by 11x. Now it takes around 120ms to execute. What's interesting is that if I remove either the LEFT JOIN clause or the ORDER BY id DESC , the time goes back to 10ms.

I am new to databases so I don't understand this. Why is it that removing either one of these clauses speeds it up 11x ? And how can I keep it as is but make it faster?

I have indexes on spentits.user_id, follows.follower_id, follows.accepted, and on primary ids of each table.

EXPLAIN:

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

Answer 1

You should have index also on:

wishlist_items.spentit_id

Because you are joining over that column