Mysql-SQL optimization-count the number of certain types _ MySQL

Sometimes we want to count the number of such SQL statements. In addition to full table scanning, filesort takes 134 seconds. Mysqlselectcountry, count (*) fromt1groupbycountry; + --------- + ---------- + | country | count

Sometimes we want to count the number of such SQL statements. In addition to full table scanning, filesort takes 1.34 seconds.

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)

The following are two optimization methods: Full table scan, but it is estimated that count () is less CPU than sum (), and the method is better.

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)

Version: mysql5.5.30