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]