MySQL ROW COUNT

How can we get the number of rows in the MySQL table?

Row count means how many records are available in the database. It is a key activity for finding out and monitoring the growth of the table during development and operations. The simplest and popular function that allows us to return the number of records available in the table is the MySQL COUNT() function. It is an aggregate function that works on the entire set of records and produces a single summarized output.

We can use the MySQL COUNT() function to accomplish the following things:

• To return the number of records available in a table.
• To return the number of rows by a query or an aliased table.
• To get the count of non-null values in a column.
• To get the count of distinct or unique values in a column.

Syntax:

The following are the syntax to get the row count of a single table in MySQL:

Let us take the following tables for demonstration:

Table: employee

Table: orders

For example, if we want to get the total number of rows in the employee table, we need to use the syntax as follows:

We should get the output like below screenshot:

Getting MySQL Row Count of Two or More Tables

If we want to get the row count of two or more tables, it is required to use the subqueries, i.e., one subquery for each individual table.

Example

Suppose we want to get the row count of employee and orders tables in a single query, we must execute the query as follows:

Output:

We will get the result like below output:

We can also use the UNION operator to get the row count of two or more tables where the operator combines the result returned by the individual SELECT query:

We will get the output as follows:

Getting MySQL Row Count of All Tables in a Particular Database

MySQL also allows us to get the number of rows of all tables in a specific database. The following are the steps that help us to count the number of rows of all tables in a particular database:

Step 1: First, we need to get all table names available in a database.

Step 2: Second, create a SQL statement that includes count query for all tables separated by the UNION operator.

Step 3: Finally, execute the query using MySQL Prepared Statement.

To get all table names of particular database tables, execute the following query from the information_schema database:

The above mentioned database has the following tables:

Next, construct the SQL statement. Here, we will use the GROUP_CONCAT and CONCAT functions to construct the statement:

In the above query, the table_list is the names of the tables available in a particular database, resulting from the query used in the first step. So, the above SQL query becomes:

If you are using the MySQL version 8.0 or above, you can use a MySQL CTE (common table expression) instead of a derived table:

Third, execute the @sql statement using the prepared statement as follows:

After execution of the above statement, we will get the output as follows:

Getting MySQL Row Count of All Tables in a Database with Single Query

MySQL also provide a query to count rows of all tables in a particular database. The quickest way is to querying data from the information_schema database directly. See the below statement:

After execution, we will get the result as follows:

Sometimes this query does not returns accurate result. It is because the actual row count in the tables and the row count in the information_schema are not synchronized. We can avoid this inaccurate result by running the ANALYZE TABLE statement before querying row count from information_schema database.