Argument-difference between select count (*) and select count (1),-selectcount

Argument-difference between select count (*) and select count (1),-selectcount

Preface: Today, I saw a blog reposted by my colleague titled "The difference between select count (*) and select count (1)", which is due to interest, just like verifying the conclusions in this article:

From the content point of view, there is a primary key and no primary key, then

Step 1: create two tables: test1 (with a primary key) and test2 (without a primary key)

CREATE TABLE `test1` (  `id` int(12) NOT NULL AUTO_INCREMENT,  `value` int(12) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=400000 DEFAULT CHARSET=utf8;

CREATE TABLE `test2` (  `id` int(12) NOT NULL,  `value` int(12) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2: insert 40 thousand data records

Drop procedure inserttest; create procedure inserttest () BEGINDECLARE I int default 1; WHILE I <400000 DO -- test1 table is test1, test2 table is test2INSERT INTO test1 VALUES (I, I ); SET I = I + 1; END WHILE; ENDCALL inserttest;

Step 3: demonstrate the first viewpoint: "If the table has no primary key, count (1) is faster than count (*)." We tested 10 times (the test2 table has no primary key, only 10 are listed in length)

SELECT SQL_NO_CACHE COUNT(1) as ooo FROM test2;SELECT SQL_NO_CACHE COUNT(*) as aaa FROM test2;

Result data

Times	Count (1)	Count (*)
1	Time: 0.107 s	Time: 0.105 s
2	Time: 0.106 s	Time: 0.105 s
3	Time: 0.105 s	Time: 0.106 s
4	Time: 0.106 s	Time: 0.106 s
5	Time: 0.106 s	Time: 0.108 s
6	Time: 0.106 s	Time: 0.103 s
7	Time: 0.107 s	Time: 0.106 s
8	Time: 0.104 s	Time: 0.103 s
9	Time: 0.105 s	Time: 0.105 s
10	Time: 0.105 s	Time: 0.110 s

Conclusion: This opinion is not trustable. From the result data, the average time of count (1) is a little shorter than that of count (*), but it is not always the same.. Step 4: demonstrate the second point of view: "When a table has a primary key, the primary key is the fastest as the count condition." We tested it 10 times (the test1 table has a primary key, and only 10 pieces of space are listed)

SELECT SQL_NO_CACHE COUNT(1) as ooo FROM test1;SELECT SQL_NO_CACHE COUNT(*) as aaa FROM test1;SELECT SQL_NO_CACHE COUNT(id) as aaa FROM test1;

Result data (three more columns are not allowed)

Times	Count (1)	Count (*)	Count (id)
1	Time: 0.052 s	Time: 0.053 s	Time: 0.059 s
2	Time: 0.053 s	Time: 0.052 s	Time: 0.060 s
3	Time: 0.052 s	Time: 0.052 s	Time: 0.059 s
4	Time: 0.052 s	Time: 0.053 s	Time: 0.060 s
5	Time: 0.054 s	Time: 0.052 s	Time: 0.059 s
6	Time: 0.052 s	Time: 0.054 s	Time: 0.059 s
7	Time: 0.051 s	Time: 0.053 s	Time: 0.060 s
8	Time: 0.052 s	Time: 0.053 s	Time: 0.061 s
9	Time: 0.054 s	Time: 0.053 s	Time: 0.059 s
10	Time: 0.071 s	Time: 0.052 s	Time: 0.062 s
11	Time: 0.051 s	Time: 0.052 s	Time: 0.059 s
12	Time: 0.051 s	Time: 0.052 s	Time: 0.059 s
13	Time: 0.052 s	Time: 0.053 s	Time: 0.059 s

Conclusion: count is faster when the table has a primary key than when there is no primary key, but the primary key is the slowest when it is used as the count condition. count (1) has better performance in most cases.Step 5: I would like to demonstrate that "the table has only one field but the count (*) is the fastest", but in rare cases, the table has only one field, so I will not discuss it. Step 6: demonstrate that "count (*) and count (1) include null, while count (column name) null value is not included. "First, we can change the value column of the test1 table to null.

ALTER TABLE `test1`MODIFY COLUMN `value`  int(12) NULL AFTER `id`;

Modify 13 rows of data to null.

UPDATE `test1` SET `value`=NULL WHERE (`id`='13')

Use the following statement

SELECT SQL_NO_CACHE COUNT(1) as ooo FROM test1;SELECT SQL_NO_CACHE COUNT(*) as aaa FROM test1;SELECT SQL_NO_CACHE COUNT(id) as aaa FROM test1;SELECT SQL_NO_CACHE COUNT(value) as aaa FROM test1;

Data evidence

Count (1)	Count (*)	Count (id)	Count (value)
399999	399999	399999	399998

Conclusion: When count (*) and count (1) are used, the statistics include null, and only count (a column with null values) does not contain null values.. Step 7: For the use of select sum (1), it mainly means that the calculation result of the sum method is the value of 1 or the product of other values and the number of rows meeting the condition, that is, sum (1) it can also be used to count the number of rows. Then I tested the performance (test the table test1 with a null value and a primary key). The data is as follows:

Times	Count (1)	Count (*)	Count (id)	Count (value)	SUM (1)
1	Time: 0.052 s	Time: 0.052 s	Time: 0.060 s	Time: 0.091 s	Time: 0.104 s
2	Time: 0.054 s	Time: 0.051 s	Time: 0.060 s	Time: 0.090 s	Time: 0.105 s
3	Time: 0.053 s	Time: 0.051 s	Time: 0.059 s	Time: 0.093 s	Time: 0.100 s
4	Time: 0.052 s	Time: 0.053 s	Time: 0.060 s	Time: 0.094 s	Time: 0.102 s
5	Time: 0.051 s	Time: 0.052 s	Time: 0.059 s	Time: 0.091 s	Time: 0.102 s

Conclusion: When sum (1) is used as the number of rows, the performance is the worst..
Tips: Maybe my test results are inconsistent with those of the original author. If you have any misunderstanding, please correct me..