mysql 通过一张表的字段统计另一张表的数量查询速度过慢，有什么办法优化吗

码拜

8年 ago

有表open_city和buyerUser
open_city结构有city_id和city_name
buyerUser有city_id且可重复有多个
现在需要统计buyerUser中的city_id有多少个数量，写了sql以后发现查询速度太慢，有什么方法可以优化吗？
下面是sql语句：
select DISTINCT city_name as city,
(select count(*) from buyerUser where buyerUser.city_id=open_city.city_id and buyerUser.ISVerify=”1″ and Ub_state=1)
from open_city;

解决方案

这个查询的效率怎样？
SELECT city_id, count(*)
FROM buyerUser
WHERE buyerUser.ISVerify = “1”
AND Ub_state = 1;
group by city_id
假如也慢，建议如下3个列的复合索引
ISVerify, Ub_state, city_id

SELECT  a.city_name AS city ,
        COUNT(b.city_id) AS con
FROM    open_city AS a
        LEFT JOIN buyerUser AS b ON a.city_id = b.city_id
                                    AND b.ISVerify = 1
                                    AND Ub_state = 1
GROUP BY a.city_name;

引用:

Quote: 引用:
这个查询的效率怎样？
SELECT city_id, count(*)
FROM buyerUser
WHERE buyerUser.ISVerify = “1”
AND Ub_state = 1;
group by city_id
假如也慢，建议如下3个列的复合索引
ISVerify, Ub_state, city_id

谢谢啦~
不过你这条sql只能查询一条数据的结果，而且统计的数量也不对，可能是本人没有把问题说清楚。
open_city表中的city_id是唯一且不重复的，buyerUser表中的city_id是重复且有多个的
后面那些条件可以先不管他，主要是关联统计的时候耗时过多
需要的结果是：
city_id  count
1             0
2              100
3            6

2 楼 MythKing 的答案其实是没错的，只需要在buyerUser表，按city_id 分组统计，group by的速度是非常快的。
但这样的结果，假如在buyerUser表没对应city_id的记录的话，是没有那条的，就是你示例的第一条没有。
想得到你需要的结果的话，再和open_city表做个左连接就好了，这样

select a.city_id, ifnull(cc, 0) from open_city a left join
(SELECT city_id, count(*) as cc
FROM buyerUser
WHERE buyerUser.ISVerify = "1"
      AND Ub_state = 1
group by city_id)  b on a.city_id=b.city_id;

以文本方式贴出 不要贴截图
explain select …
show index from ..
以供分析。