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

Question

有表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;

Answer 1

子查询可以先直接统计出city_id，然后再去city表中查city_name，子查询如下：
select b.city_id, count(*) as count_ from buyerUser b where b.ISVerify='1' and b.Ub_state=1 group by b.city_id

Answer 2

select open_city.city_name as city,count(*)
from open_city left join buyerUser on buyerUser.city_id=open_city.city_id
where buyerUser.city_id=open_city.city_id and buyerUser.ISVerify='1' and Ub_state=1
group by open_city.city_name;

能否把表数据给出来？
这样更直观一些；

Answer 3

分开查询,分两次查询!!