Mysql——SQL优化-统计某种类型的个数,mysqlsql优化个数
有时我们想统计某种类型有多少个,会用这个SQL。全表扫描之余,还要filesort,耗时1.34秒。
mysql> select country,count(*) from t1 group by country; +---------+----------+ | country | count(*) | +---------+----------+ | NULL | 32 | | africa | 524288 | | america | 524288 | | china | 524288 | +---------+----------+ 4 rows in set (1.34 sec) mysql> desc select country,count(*) from t1 group by country; +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1573382 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ 1 row in set (0.00 sec)
以下是两种优化方法,都是全表扫描,但估计count()要比sum()耗的CPU少点,方法更佳。
mysql> select count(country='africa' or null) as africa,count(country='america' or null) as america, count(country='china' or null) as china from t1; +--------+---------+--------+ | africa | america | china | +--------+---------+--------+ | 524288 | 524288 | 524288 | +--------+---------+--------+ 1 row in set (0.78 sec) mysql> desc select count(country='africa' or null) as africa,count(country='america' or null) as america, count(country='china' or null) as china from t1; +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1573382 | | +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ 1 row in set (0.00 sec) mysql> select sum(country='africa') as africa ,sum(country='america') as america,sum(country='china') from t1; +--------+---------+----------------------+ | africa | america | sum(country='china') | +--------+---------+----------------------+ | 524288 | 524288 | 524288 | +--------+---------+----------------------+ 1 row in set (0.86 sec) mysql> desc select sum(country='africa') as africa ,sum(country='america') as america,sum(country='china') from t1; +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1573382 | | +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ 1 row in set (0.00 sec)
版本mysql5.5.30
update b set b.cnum = (select c.counts from
(SELECT cid,COUNT(*) counts FROM comment GROUP BY cid) c
where a.cid=c.cid);
哎,禁不住说一声,苦逼程序员啊,都这么晚不睡
你试试吧,按照你描述的,我这么写应该就通过了,有问题直接hi
select 指定的列看看, 还有尽量用索引来搜索吧
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。