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:
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; …
[Read more]