There are several ways to get a row count in MySQL. Some database management products provide database statistics like table sizes, but it can also be done using straight SQL. In today’s tip, we’ll use the native COUNT() function to retrieve the number of rows within one table or view within a MySQL database. In part 2, we’ll learn how to obtain a row count from multiple tables, or even from all of the tables within a database.

The COUNT() Function’s Many Forms

You probably already know that the COUNT() function returns the number of rows in a table. But there’s a little more to it than that, as the COUNT() function can be utilized to count all rows in a table or only those rows that match a particular condition. The secret is in the function signatures, of which there are several forms: COUNT(*), COUNT(expression) and COUNT(DISTINCT expression).

In each case, COUNT() returns a BIGINT that contains either the number of matching rows, or zero, if none were found.

Counting all of the Rows in a Table

To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.

A statement like the one above that invokes the COUNT(*) function without a WHERE clause or additional columns, will perform very fast on MyISAM tables because the number of rows is stored in the table_rows column in the tables table of the information_schema database.

For transactional storage engines such as InnoDB, storing an exact row count is problematic because InnoDB does not keep an internal count of rows in a table. If it did, concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction. What that means is that running a query with COUNT(*) during a heavy workload could result in slightly inaccurate numbers.

Counting only Non-null Rows with COUNT(expr)

Passing nothing to COUNT() executes the COUNT(expr) version of the function, but sans parameter. Invoking COUNT() in that way only returns rows which are not comprised of NULL values. For example, say that we had a simple table called code_values:

Selecting COUNT() from the table would return 2, even though there are 4 rows:

+---------+
| COUNT() |
+---------+
| 2       |
+---------+

Note that this version of the COUNT() function is rarely used because NULL rows should not be an issue in normalized databases, a condition that could only happen if the table didn't have a primary key. In most cases, COUNT(*) will work just fine.

Of course, COUNT(expr) does accept proper expressions. Here’s another query that fetches NULL and non-NULL rows alike:

Counting Non-null Values

The COUNT(expr) version of the COUNT function also accepts individual column names, the effect of which is that COUNT(column_name) will return the number of records where column_name is not NULL. Hence, the following SELECT query would fetch the number of rows where the description column contained a non-NULL value:

In Part 2 we’ll learn how to use the COUNT(DISTINCT expression) signature as well as how to obtain a row count from multiple tables.