This function executes as a window function if over_clause  is present. over_clause  is as described in Section 12.21.2, “Window Function Concepts and Syntax” . 

mysql> SELECT student.student_name,COUNT(*) FROM student,course WHERE student.student_id=course.student_id GROUP BY student_name; 

COUNT(*)  is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL  values. 

For transactional storage engines such as InnoDB , storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count. 

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. 

As of MySQL 8.0.13, SELECT COUNT(*) FROM tbl_name  query performance for InnoDB  tables is optimized for single-threaded workloads if there are no extra clauses such as WHERE  or GROUP BY . 

InnoDB  processes SELECT COUNT(*)  statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB  processes SELECT COUNT(*)  statements by scanning the clustered index. 

Processing SELECT COUNT(*)  statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, use SHOW TABLE STATUS . 

InnoDB  handles SELECT COUNT(*)  and SELECT COUNT(1)  operations in the same way. There is no performance difference. 

For MyISAM  tables, COUNT(*)  is optimized to return very quickly if the SELECT  retrieves from one table, no other columns are retrieved, and there is no WHERE  clause. For example: 

mysql> SELECT COUNT(*) FROM student; 

This optimization only applies to MyISAM  tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1)  is only subject to the same optimization if the first column is defined as NOT NULL . 

• COUNT(DISTINCT expr ,[ expr ...]) 

Returns a count of the number of rows with different non- NULL expr  values. 

If there are no matching rows, COUNT(DISTINCT)  returns 0 . 

mysql> SELECT COUNT(DISTINCT results) FROM student; 

In MySQL, you can obtain the number of distinct expression combinations that do not contain NULL  by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...) . 

