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

Preface

The count function is used to count the records in a table or array. count(*) returns the number of retrieved rows, regardless of whether it contains NULL values. I feel like everyone is discussing the difference in count recently, so I’ll write it down as well: Welcome to leave a message to discuss. Without further ado, let’s take a look at the detailed introduction.

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 why is "key_length": "33" 33? What is a secondary index? See next section

There is no difference between count(*) and count(1), but there is a difference between count(col)

The execution plan has characteristics: it can be seen that it does not query indexes and tables, and sometimes select tables optimized away will appear, which will not query tables and will be very fast.

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

For explains on simple count queries (ie 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's meaning of "Select tables optimized away" is not "nothing better can be optimized". The key point in the official explanation is:
MySQL can read the result directly

So, a reasonable explanation is:

1 The data is already in memory and can be read directly;

2 Data can be considered as a result of a calculation, 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 there is "no need to perform the select".

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.