Understanding the Implementation Differences Between COUNT(*) and COUNT(column) in MySQL

Continuing from the previous article about how the number of selected fields affects query efficiency, this piece examines the implementation differences between COUNT(*) and COUNT(column) when using InnoDB as the storage engine.

Example usage

The following statements illustrate that the two counts can return different results:

mysql> show create table baguai_f \G
*************************** 1. row ***************************
Table: baguai_f
Create Table: CREATE TABLE `baguai_f` (
  `id` int(11) DEFAULT NULL,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  `c` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from baguai_f;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| 1    | g    | g    | NULL |
| 1    | g1   | g1   | g1   |
| 3    | g2   | g2   | g2   |
| 4    | g    | g    | NULL |
| 5    | g    | g    | NULL |
| 6    | g3   | g3   | g3   |
+------+------+------+------+
6 rows in set (0.00 sec)

mysql> desc select count(*) from baguai_f where b='g';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | baguai_f | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select count(c) from baguai_f where b='g';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | baguai_f | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select count(*) from baguai_f where b='g';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(c) from baguai_f where b='g';
+----------+
| count(c) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

The discrepancy arises because column c contains only NULL values for rows where b='g' , so COUNT(c) excludes those rows.

Data flow for COUNT(*)

MySQL layer builds a read_set that includes only column b (the column used in the WHERE clause).

InnoDB constructs a template containing only column b .

The engine loops over each row, returning the value of b to MySQL.

MySQL applies the b='g' filter.

MySQL performs the COUNT aggregation; because COUNT(*) does not consider NULL , every filtered row increments the counter.

The relevant source code for the aggregation is:

bool Item_sum_count::add()
{
  if (aggr->arg_is_null(false))
    return 0;
  count++;
  return 0;
}

Data flow for COUNT(c)

The read_set now contains two columns, b and c , because the value of c must be examined for NULL .

InnoDB builds a template with two fields.

During the loop, both columns are returned to MySQL.

MySQL filters rows with b='g' .

When aggregating, the same Item_sum_count::add() function checks arg_is_null(false) ; rows where c is NULL are skipped, resulting in a count of 0.

Thus the only real difference is the extra column read and the NULL‑check step, which makes COUNT(*) slightly more efficient under identical execution plans.

Summary of differences

InnoDB returns all rows in both cases.

MySQL filters rows by the WHERE condition.

For COUNT(c) , MySQL additionally filters out rows where c is NULL .

COUNT(*) does not need the extra column and therefore avoids one step.

Overall, the performance gap is minimal, but COUNT(*) can be marginally faster.

For readers who want a deeper dive into MySQL internals, the author recommends the book “深入理解 MySQL 主从原理 32 讲”.

Author: 高鹏 (WeChat: gp_22389860)

Community announcements

Free Mycat problem diagnosis and source‑code analysis.

Call for original technical articles on MySQL, DBLE, DTLE, with a reward of a JD e‑card and community merchandise.

Feel free to like, share, or comment on the article.