mysql - 如何优化这个 "where IN"和 "where NOT IN"查询？

Question

下面的query/查询得到用户去过的城市，得到用户去过的地方；并返回用户未去过的城市中的地点。

// I get the city_id and object_id. Each vote has the place_id and its city_id.
  SELECT DISTINCT city_id as city_id, object_id as object_id
    FROM vote
   WHERE object_model = 'Place'
     AND user_id = 20
ORDER BY created_at desc

// I build an array with city_ids and another with object_ids
$city_ids = array(...);
$place_ids = array(...);

我得到了用户在他去过的城市里没有去过的地方 - 1 秒

  SELECT id, title
    FROM place
   WHERE city_id IN ($city_ids)
     AND id NOT IN ($place_ids)
ORDER BY points desc
   LIMIT 0,20

解释 SQL

select_type table   type    possible_keys           key             key_len   ref    ows     Extra
-----------------------------------------------------------------------------------------------------------
SIMPLE      p       range   PRIMARY,city_id_index    city_id_index    9         NULL  33583    Using where; Using filesort

另一种优化尝试是使用 LEFT JOIN/IS NULL 和子查询执行一个查询，但它需要更长的时间(30+ 秒)

   SELECT id, title 
     FROM place AS p
LEFT JOIN vote v ON v.object_id = p.id
                AND v.object_model = 'Place'
                AND v.user_id = 20
    WHERE p.city_id IN (SELECT city_id 
                          FROM vote 
                         WHERE user_id = 20 
                           AND city_id != 0)
      AND v.id is null
 ORDER BY p.points desc
    LIMIT 0, 20

假设我们可以为每个用户提供包含 500 个城市和 1000 个地点的数组，您将如何进行查询？当有很多 ID 时，哪个是 where in 和 where NOT IN 的最佳替代方案？

Answer 1

我不是 MySQL 专家，但我的查询看起来并不太复杂。我不会关注查询，而是关注索引。也许以下索引会有所帮助:

CREATE INDEX vote_index1 ON vote (user_id, city_id)
CREATE INDEX vote_index2 ON vote (object_id, object_model, user_id)