mysql - 如何加快从两个表中搜索的 sql 查询？

Question

这是一个例子，所以我有表 user 和表 city，它们是这样的:

user 列是 (user_id, city_id, timestamp) [user_id 和 city_id 是唯一的]

city 列是 (city_name, city_id) [city_id is unique]

我想从某个城市获取给定日期的用户数，所以基本上我是这样做的:

select city_id, city_name, 
    (select count(user.user_id) 
     from user, city 
     where DATE_FORMAT(user.timestamp, '%Y-%m-%d') = '2017-03-07' 
     and user.city_id = ct.city_id) as user_count
from city ct 
where (city_id = 20 or city_id = 30)

结果:

city_id, city_name, user_count
20       New York   100
30       LA         200

然后我意识到这比直接搜索要慢得多

select count(user.user_id) 
from user, city 
where DATE_FORMAT(user.timestamp, '%Y-%m-%d') = '2017-03-07' 
    and user.city_id = 20

这是为什么？原来搜索的ct.city_id不是已经设置为20或者30了吗？我应该如何优化搜索并以我想要的表格格式获得结果？

Answer 1

您可以改进您的查询，避免使用子选择并使用内部联接和分组依据

select city_id, city_name,    count(user.user_id) 
from user
inner join city on user.city_id = city.city_id
where DATE_FORMAT(user.timestamp, '%Y-%m-%d') = '2017-03-07' 
and city_id in (city_id = 20 or city_id = 30)
group by city_id, city_name