The difference between count(1), count(col1), count(*)

The key to the difference between count(1), count(col1), count(*) is the execution plan of the three. The different execution plans of the three will lead to inconsistent results.

count(1) is equivalent to the count of select 1 from table, 1 is just a constant, not the column number

count(col1) is a field in the count table, which distinguishes the difference between an index and no index on the field

count(*) is equivalent to the count of select * from table, select * must scan the entire table

MySQL and Oracle have adopted different execution plans for the above situation, and the execution plan will be very interesting. The following use mysql and oracle test cases to observe the difference in count.

The test cases of mysql and oracle are similar, and the table structure and data distribution are similar:

mysqloracle
CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idxc` (` c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 CREATE TABLE t1 (
  a number,
  b varchar2(10),
  c number,
constraint pk_lzl primary key(a));
 create index idx_c on t1(c);
+----+------+------+
| a | b | c |
+----+------+------+
| 0 | 0 | 0 |
| 1 | 1 | 1 |
| 4 | 4 | 4 |
| 10 | 10 | 10 |
+----+------+------+
+----+------+------+
| a | b | c |
+----+------+------+
| 0 | 0 | 0 |
| 1 | 1 | 1 |
| 4 | 4 | 4 |
| 10 | 10 | 10 |
+----+------+------+

The execution plan of mysql and oracle for count(1), count(col1), count(*):

mysqloracle

1. In the mysql execution plan, the execution plan has an index (key is not empty), and type=index, then the execution plan is a full index scan, which is the index full scan in Oracle

2. Count(1), count(col1), count(*) both Oracle and MySQL use index full scan. The index full scan method starts from finding the first leaf node of the B+ tree, and searches all leaf node blocks backward through the doubly linked list. This access method also applies to the primary key, so there is INDEX FULL SCAN | PK_LZL in the ORACLE execution plan

3. In the scenario of count(col1), oracle and mysql use the same execution plan. As in the example, the common index on column t1.c is accessed

4. In the count(1), count(*) scenarios, although both MySQL and Oracle use the full scan of the index, why does Oracle use the full scan of the primary key while MySQL chooses to use the full scan of the secondary index?

Because the B+ tree index structure of mysql and oracle is different. The B+ tree index of mysql innodb will store the null value, and it will put the null at the left end of the index tree, but the Oracle B+ tree index will not store the null value. And count(1) and count(*) should be the number of rows in the table, regardless of whether the row is null or not.

For oracle, if you access the index on the column at count(1) or count(*), it will result in rows with null values ​​on the column count less than the count value calculated is incorrect, so oracle in The primary key must be used for count(1) and count(*) (if there is no primary key, full table scan is used), the primary key will not be empty.

For mysql, in count(1) or count(*), it is feasible to access the primary key or the secondary index. In terms of cost, the secondary index is smaller than the primary key because the secondary index only stores the index Field and primary key ID, the primary key stores all fields and primary key IDs. So mysql chose to access the smaller object.

Since mysql index stores null values, will null be calculated when count(col1)?

Insert a record in the oracle and mysql tables just now

insert into t1 values(20,'20',null);

Compare the difference between the count(col1) results of oracle and mysql

oraclemysql

SQL> select count(c) from t1;

  COUNT(C)
----------
         4

mysql> select count(c) from t1;
+----------+
| count(c) |
+----------+
| 4 |
+------ ----+

SQL> select count(1) from t1;

  COUNT(1)
----------
         5


mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 5 |
+------ ----+

Although the mysql index stores null values, count(col1) still does not calculate null, even if it is the accessed index.

The difference between count(1), count(col1), count(*) is summarized as follows:

1.count(1)=count(*), which calculates the actual number of rows in the table, regardless of whether the row is empty

2.count(col1) calculates the number of rows where the col1 column is not empty

3. In mysql, count(1), count(col1), count(*) all access the secondary index

4. In Oracle, only count (col1) accesses the index, count (1) and count (*) both access the primary key or table

5. The difference in access mode is because the mysql index stores the null value, and the oracle index does not store the null value. Oracle must access the primary key or table to count the true number of rows in the table, while MySQL accesses a smaller secondary index to count the true number of rows in the table.