Summary of differences between count (*), count (1), and count (col) in MySQL, mysqlcol

Summary of differences between count (*), count (1), and count (col) in MySQL, mysqlcol

Preface

The count function is a function used to count records in tables or arrays. count (*) returns the number of rows to be retrieved, whether or not it contains NULL values. Recently I feel that everyone is discussing the difference between count. Let me also write it down: you are welcome to leave a message to discuss the difference. Let's take a look at the details.

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(1,10);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('368rhf8fj',null);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 here "key_length": "33", why is it 33? What is a secondary index? See the following section.

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

The execution plan has the following features: we can see that it does not query indexes and tables. Sometimes select tables optimized away does not look up the table, and the speed will be very fast.

Extra sometimes displays "Select tables optimized away", which means there is no better optimization.

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 "no better optimization is available" for "Select tables optimized away". The key points in the official explanation are:
MySQL can read the result directly

Therefore, a reasonable explanation is:

1. Data can be directly read in the memory;

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

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

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.