mysql查询优化not in_mysql – 如何优化这个“IN”和“where NOT IN”查询？

以下查询/查询获取用户访问过的城市,获取用户访问过的地点;并返回用户尚未进入的城市中的地点.

// 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

EXPLAIN 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时,哪个是哪个地方以及哪里不是IN的最佳替代方案？