Mysql -- SQL Optimization-count the number of Certain types and the number of mysqlsql Optimizations

Mysql -- SQL Optimization-count the number of Certain types and the number of mysqlsql Optimizations

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

Mysql database, SQL statement, update the statistical quantity in one table to two tables

Update B set B. cnum = (select c. counts from
(SELECT cid, COUNT (*) counts FROM comment group by cid) c
Where a. cid = c. cid );

Ah, I can't help but say, it's hard for programmers to stay asleep so late.

Try it. According to your description, I should have passed this writing.

Mysql SQL statement Optimization

Check the columns specified by the select statement. Try to use indexes for search.