MySQL Cookbook, 2nd Edition

Summarizing with COUNT()

Problem

You want to count the number of rows in a table, the number of rows that match certain conditions, or the number of times that particular values occur.

Solution

Use the COUNT() function.

Discussion

To count the number of rows in an entire table or that match particular conditions, use the COUNT() function. For example, to display the contents of the rows in a table, you can use a SELECT * statement, but to count them instead, use SELECT COUNT(*). Without a WHERE clause, the statement counts all the rows in the table, such as in the following statement that shows how many rows the driver_log table contains:

mysql>SELECT COUNT(*) FROM driver_log;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+

If you don’t know how many U.S. states there are, this statement tells you:

mysql>SELECT COUNT(*) FROM states;
+----------+
| COUNT(*) |
+----------+
|       50 |
+----------+

COUNT(*) with noWHERE clause is very quick for MyISAM tables. However, for BDB or InnoDB tables, you may want to avoid it because the statement requires a full table scan, which can be slow for large tables. If an approximate row count is all you require, a workaround that avoids a full scan for those storage engines is to extract theTABLE_ROWS value from the INFORMATION_SCHEMA database:

mysql>SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'states';
+------------+
| TABLE_ROWS |
+------------+
|         50 |
+------------+

Before MySQL 5.0, INFORMATION_SCHEMA ...