mysql - 如何使 JOINS 更快？

Question

我从这个查询开始:

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

执行此查询需要 10 毫秒。

但是一旦我添加了一个简单的加入:

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

此执行时间增加了 11x。现在它需要大约 120ms 来执行。有趣的是，如果我删除 LEFT JOIN 子句 或 ORDER BY id DESC ，时间会回到 10ms。

我是数据库的新手，所以我不明白这一点。为什么删除这些子句中的任何一个都会加快 11x 的速度？我怎样才能保持原样但让它更快？

我在 spentits.user_id、follows.follower_id、follows.accepted 和 primary ids 上有索引> 每张 table 。

解释:

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

你还应该有索引:

wishlist_items.spentit_id

因为你加入了那个专栏