Looking at how people are using COUNT(*) and COUNT(col), it looks like most of them think they are synonyms and just use what they happen to like, while there is a substantial difference in performance and even query results. Also, we find a difference in execution on InnoDB and MyISAM engines.
NOTE: All tests were applied for MySQL version 8.0.30, and in the background, I ran every query three to five times to make sure that all of them were fully cached in the buffer pool (for InnoDB) or by the filesystem (for MyISAM).
Count function for Innodb engine:
Let’s have look at the following series of examples for InnoDB engine:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | CREATE TABLE count_innodb ( id int(10) unsigned NOT NULL AUTO_INCREMENT, val_with_nulls int(11) default NULL, val_no_null int(10) unsigned NOT NULL, PRIMARY KEY idx (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; (mysql) > select count(*) from count_innodb; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (0.38 sec) (mysql) > select count(val_no_null) from count_innodb; +--------------------+ | count(val_no_null) | +--------------------+ | 10000000 | +--------------------+ 1 row in set (0.38 sec) |
In this InnoDB engine, we can see that it requires some time to get COUNT(*) and COUNT(val_no_null) of rows for the table, and as we will see further, MyiSAM is significantly faster compared to InnoDB table in the sense of getting an answer for COUNT(*).
But why we can’t just cache the actual number of the rows? InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction. By the way, we can use information schema to instantly get approximately the number of rows of the table in question:
1 2 3 4 5 6 7 | (mysql) > select table_rows from information_schema.tables where table_name='count_innodb'; +------------+ | TABLE_ROWS | +------------+ | 9980586 | +------------+ 1 row in set (0.00 sec) |
As you can see it’s not the exact number of rows. However, sometimes a rough count might be sufficient.
Let’s take a look into COUNT(val_with_nulls);
1 2 3 4 5 6 7 | (mysql) > select count(val_with_nulls) from count_innodb; +-----------------------+ | count(val_with_nulls) | +-----------------------+ | 9990001 | +-----------------------+ 1 row in set (2.14 sec) |
And there, as you can see we have differences in the result of COUNT(*) vs COUNT(val_with_nulls)
Why? Because the val_with_nulls column is not defined as NOT NULL there can be some NULL values in it and so MySQL has to perform a table scan to find out. This is also why the result is different for the second query
So COUNT(*) and COUNT(col) queries not only could have substantial performance differences but also ask different questions.
Let’s have another round of queries, there let’s take a look at how InnoDB manages to do COUNT(*), COUNT(val_no_null), COUNT(val_with_nulls) with the same WHERE clause:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | (mysql) > select count(*) from count_innodb where id<1000000; +----------+ | count(*) | +----------+ | 980000 | +----------+ 1 row in set (0.30 sec) (mysql) > explain select count(*) from count_innodb where id<1000000G *************************** 1. row *************************** select_type: SIMPLE table: count_innodb type: range possible_keys: PRIMARY key: PRIMARY rows: 1955802 filtered: 100.00 Extra: Using where; Using index (mysql) > select count(val_no_null) from count_innodb where id<1000000; +--------------------+ | count(val_no_null) | +--------------------+ | 980000 | +--------------------+ 1 row in set (0.33 sec) (mysql) > explain select count(val_no_null) from count_innodb where id<1000000G *************************** 1. row *************************** select_type: SIMPLE table: count_innodb type: range possible_keys: PRIMARY key: PRIMARY rows: 2013804 filtered: 100.00 Extra: Using where |
We can see the performance of the query is equal for both cases, and it has only differences of 10%, and if you pay closer attention to EXPLAIN for COUNT(*) query, you will notice Using index. This means that MySQL can use only the index and does not touch the rest table data, which might be sufficient to get the count of rows for huge tables.
You might want to use columns that already have an index to speed up the query for huge tables.
Will we have any surprises with COUNT(val_with_nulls)? Let’s see:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | (mysql) > select count(val_with_nulls) from count_innodb where id<1000000; +-----------------------+ | count(val_with_nulls) | +-----------------------+ | 970001 | +-----------------------+ 1 row in set (0.33 sec) (mysql) > explain select count(val_with_nulls) from count_innodb where id<1000000G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: count_innodb partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1955802 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) |
No surprises; we can see the performance of the query is pretty even across all COUNT(*), COUNT(val_with_nulls), COUNT(val_with_nulls).
Count function for MyISAM engine:
Now let’s take a look into COUNT() function for the MyISAM engine:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | CREATE TABLE count_myisam ( id int(10) unsigned NOT NULL, val_with_nulls int(11) default NULL, val_no_null int(10) unsigned NOT NULL, KEY idx (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; (mysql) > select count(*) from count_myisam; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (0.00 sec) (mysql) > select count(val_no_null) from count_myisam; +--------------------+ | count(val_no_null) | +--------------------+ | 10000000 | +--------------------+ 1 row in set (0.00 sec) |
What flash speed we saw there!
As this is a MyISAM table, we have cached the number of rows inside of the table, this is how the MyISAM engine works. That is why it can instantly answer COUNT(*) and COUNT(val_no_null) queries.
Please, pay attention to the difference between engines: InnoDB is a transaction engine, and MyISAM is a non-transactional storage engine.
1 2 3 4 5 6 7 | (mysql) > select count(val_with_nulls) from count_myisam; +-----------------------+ | count(val_with_nulls) | +-----------------------+ | 9990001 | +-----------------------+ 1 row in set (14.18 sec) |
But when it comes to COUNT(val_with_nulls) for MyISAM table we can see that’s a slower InnoDB in 7 times; what a huge difference. Also, we can see the same behavior for COUNT(val_with_nulls), as NULL values obviously will not be considered. MySQL Optimizer does a good job in this case, doing a full table scan only if it is needed because the column can be NULL.
Now let’s try a few more queries for MyISAM table with WHERE clause:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | (mysql) > select count(*) from count_myisam where id<1000000; +----------+ | count(*) | +----------+ | 1001237 | +----------+ 1 row in set (0.41 sec) (mysql) > explain select count(*) from count_myisam where id<1000000 \G *************************** 1. row *************************** select_type: SIMPLE table: count_myisam type: range possible_keys: idx key: idx rows: 1041561 filtered: 100.00 Extra: Using where; Using index (mysql) > select count(val_no_null) from count_myisam where id<1000000; +--------------------+ | count(val_no_null) | +--------------------+ | 1001237 | +--------------------+ 1 row in set (2.55 sec) (mysql) > explain select count(val_no_null) from count_myisam where id<1000000\G *************************** 1. row *************************** select_type: SIMPLE table: count_myisam type: range possible_keys: idx key: idx rows: 1041561 filtered: 100.00 Extra: Using index condition; Using MRR (mysql) > select count(val_with_nulls) from count_myisam where id<1000000; +-----------------------+ | count(val_with_nulls) | +-----------------------+ | 1000281 | +-----------------------+ 1 row in set (2.55 sec) (mysql) > explain select count(val_with_nulls) from count_myisam where id<1000000\G *************************** 1. row *************************** select_type: SIMPLE table: count_myisam type: range possible_keys: idx key: idx rows: 1041561 filtered: 100.00 Extra: Using index condition; Using MRR |
As you can see, even if you have a WHERE clause, performance for COUNT(*) and COUNT(col) can be significantly different. In fact, this example shows a five times performance difference because all data fits in memory (for your information, as it’s the MyISAM engine, caching of data happens in the filesystem cache level). For IO-bound workloads, you frequently can see even a 100 times performance difference in this case.
The COUNT(*) query can use a covering index even while COUNT(col) can’t. Of course, you can extend the index to be (id,val_with_nulls) and get the query to be index covered again, but I would use this workaround only if you can’t change the query (ie, it is a third-party application) or case of when the column name is in the query for a reason, and you need a count of non-NULL values.
It is worth to note in this case, MySQL Optimizer does not do a good job of optimizing the query. One could notice (val_no_null) column is not null, so COUNT(val_no_null) is the same as COUNT(*), and so the query could be run as an index-covered query. It does not, and both queries have to perform row reads in this case.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | (mysql) > alter table count_myisam drop key idx, add key idx (id,val_with_nulls); Query OK, 10000000 rows affected (1 min 38.71 sec) Records: 10000000 Duplicates: 0 Warnings: 0 (mysql) > select count(val_with_nulls) from count_myisam where id<1000000; +-----------------------+ | count(val_with_nulls) | +-----------------------+ | 1000281 | +-----------------------+ 1 row in set (0.42 sec) (mysql) > select count(*) from count_myisam where id<1000000; +----------+ | count(*) | +----------+ | 1000762 | +----------+ 1 row in set (0.56 sec) |
As you can see, extending the index helps improve COUNT(val_with_nulls) query for null values about seven times compared to COUNT(val_with_nulls) without index. But also, you can see that COUNT(*) becomes around 0,6 times slower, probably because the index becomes about two times longer in this case.
At last, I want to dispel some of the delusions about COUNT(0) and COUNT(1).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | (mysql) > select count(1) from count_innodb where id<1000000; +----------+ | count(1) | +----------+ | 980000 | +----------+ 1 row in set (0.30 sec) (mysql) > select count(0) from count_innodb where id<1000000; +----------+ | count(0) | +----------+ | 980000 | +----------+ 1 row in set (0.30 sec) (mysql) > explain select count(1) from count_innodb where id<1000000G *************************** 1. row *************************** select_type: SIMPLE table: count_innodb type: range possible_keys: PRIMARY key: PRIMARY rows: 1955802 filtered: 100.00 Extra: Using where; Using index (mysql) > explain select count(0) from count_innodb where id<1000000G *************************** 1. row *************************** select_type: SIMPLE table: count_innodb type: range possible_keys: PRIMARY key: PRIMARY rows: 1955802 filtered: 100.00 Extra: Using where; Using index |
As you can see, the performance and explain of the queries are the same, and it does not really matter what number you will put inside brackets in COUNT() function. It can be whatever number you want and it will be fully equal to COUNT(*) by performance and by the actual output of this query.
I think it should be “COUNT(val_no_null)“, not COUNT(val_with_nulls)
Hello, Zonglei Dong. Good catch, thank you.