Shulou(Shulou.com)05/31 Report--

What is the difference among count (*), count (1) and count (col) in MySQL? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

1. Table structure:

Dba_jingjing@3306 > [rds_test] > CREATE TABLE `test_ count` (- > `c1` varchar (10) DEFAULT NULL,-> `c2` varchar (10) DEFAULT NULL,-> KEY `idx_ c1` (`c1`)->) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.11 sec)

2. Insert test data:

Dba_jingjing@3306 > [rds_test] > insert into test_count values (1Power10); Query OK, 1 row affected (0.03 sec) dba_jingjing@3306 > [rds_test] > insert into test_count values (abc,null); ERROR 1054 (42S22): Unknown column 'abc' in' field list'dba_jingjing@3306 > [rds_test] > insert into test_count values ('abc',null); Query OK, 1 row affected (0.04 sec) dba_jingjing@3306 > [rds_test] > insert into test_count values (null,null) Query OK, 1 row affected (0.04 sec) dba_jingjing@3306 > [rds_test] > insert into test_count values ('368rhf8 sec dba_jingjing@3306); Query OK, 1 row affected (0.03 sec) dba_jingjing@3306 > [rds_test] > select * from test_count +-+-+ | C1 | c2 | +-+-+ | 1 | 10 | | abc | NULL | | NULL | NULL | | 368rhf8fj | NULL | +-+-+ 4 rows in set (0.00 sec)

Test:

Dba_jingjing@3306 > [rds_test] > select count (*) from test_count +-+ | count (*) | +-+ | 4 | +-+ 1 row in set (0.00 sec) EXPLAIN: {"query_block": {"select_id": 1, "message": "Select tables optimized away" 1 row in set, 1 warning (0.00 sec) dba_jingjing@3306 > [rds_test] > select count (1) from test_count +-+ | count (1) | +-+ | 4 | +-+ 1 row in set (0.00 sec) EXPLAIN: {"query_block": {"select_id": 1, "message": "Select tables optimized away" 1 row in set, 1 warning (0.00 sec) dba_jingjing@3306 > [rds_test] > select count (C1) from test_count +-+ | count (C1) | +-+ | 3 | +-+ 1 row in set (0.00 sec) "table": {"table_name": "test1", "access_type": "index", "key": "idx_c1", "used_key_parts": ["C1"] "key_length": "33"

So the "key_length": "33", why 33, what is the secondary index? See the next section

There is no difference between count (*) and count (1), while count (col) is different.

The execution plan has its own characteristics: it can be seen that it does not query indexes and tables, and sometimes select tables optimized away can not look up tables, so the speed will be very fast.

Extra sometimes displays "Select tables optimized away", which means there is nothing better to optimize.

Official interpretation of For explains on simple count queries (i.e. Explain select count (*) from people) the extra

Section will read "Select tables optimized away."

This is due to the fact that MySQL can read the result directly from the table internals and therefore does not need to perform the select.

-MySQL does not mean "there is no better way to optimize" for "Select tables optimized away". The key points in the official explanation are:

MySQL can read the result directly

So, the reasonable explanation is:

1 the data can be read directly in memory

2 data can be thought of as a calculated result, such as the value of a function or expression

Once the result of the query is predicted by the optimizer, the result can be obtained without execution, so there is "not need to perform the select".

The answer to the question about what is the difference between count (*), count (1) and count (col) in MySQL is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.