MySQL select count

COUNT function counts the number of rows retrieved by a SELECT statement. The return value is of type BIGINT.

The COUNT(expr) function count the number of non-NULL values of expr in the rows retrieved by a SELECT statement.

SELECT COUNT(expr) FROM tbl;

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

SELECT COUNT(*) FROM tbl;

This means that Count(*) and Count(expr) are not equivalents. For example, if you count a not NULL column it is the same as using COUNT(*), however, if you count a column that can contain Null values, MySQL needs to scan the column values for the non-NULL values.
This fact can affect the query performance. The MYISAM table has cached number of rows in a table. This gives the ability to instantly answer COUNT(*) or COUNT of non-NULL column. However, MySQL can’t instantly answer a COUNT of a column that can contain Null values, and it must scan the column values.

Another interesting thing about the Count function, is that it is meaningless to use it together with the LIMIT clause. The LIMIT clause is limiting the result rows number. Count will always return one value, thus limiting it to a number greater than one is useless. In order to limit the count of the rows up to a certain number, you should use Limited Select Count. It is well described at http://www.mysqldiary.com/limited-select-count/.

MySQL Quiz
Leave a Reply

*