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

MySQL ROW COUNT

Table: orders

MySQL ROW COUNT

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:

MySQL ROW COUNT

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:

  mysql> SELECT       (SELECT COUNT(*) FROM employee) As Table1Count,       (SELECT COUNT(*) FROM orders) As Table2Count;  

Output:

We will get the result like below output:

MySQL ROW COUNT

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:

  mysql> SELECT ’employee’ table_name1, COUNT(*) AS row1 FROM employee UNION SELECT ‘orders’ table_name2, COUNT(*) AS row2 FROM orders;  

We will get the output as follows:

MySQL ROW COUNT

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:

  mysql> SELECT table_name FROM information_schema.tables  WHERE table_schema = ‘testdb’ AND table_type = ‘BASE TABLE’;  

The above mentioned database has the following tables:

MySQL ROW COUNT

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

  SELECT       CONCAT(GROUP_CONCAT(CONCAT(‘SELECT ”,                          table_name,                          ” table_name,COUNT(*) rows FROM ‘,                          table_name)                  SEPARATOR ‘ UNION ‘),              ‘ ORDER BY table_name’)  INTO @sql   FROM      table_list;  

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:

  SELECT CONCAT(GROUP_CONCAT(CONCAT(‘SELECT ”, table_name,                  ” table_name,COUNT(*) rows FROM ‘, table_name)                  SEPARATOR ‘UNION’), ‘ ORDER BY table_name’)  INTO @sql FROM      (SELECT table_name FROM information_schema.tables  WHERE table_schema = ‘testdb’ AND table_type = ‘BASE TABLE’) table_list;  

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:

  WITH table_list AS (  SELECT table_name FROM information_schema.tables  WHERE table_schema = ‘testdb’ AND table_type = ‘BASE TABLE’)   SELECT CONCAT(              GROUP_CONCAT(CONCAT(“SELECT ‘”,table_name,”‘ table_name,COUNT(*) rows FROM “,table_name) SEPARATOR ” UNION “),              ‘ ORDER BY table_name’)  INTO @sql  FROM table_list;  

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

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

MySQL ROW COUNT

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:

  mysql> SELECT table_name, table_rows      FROM information_schema.tables      WHERE table_schema = ‘testdb’      ORDER BY table_name;  

After execution, we will get the result as follows:

MySQL ROW COUNT

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.

Next TopicMySQL Prepared Statement

MySQL ROW COUNT

MySQL ROW COUNT

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

Syntax:

Getting MySQL Row Count of Two or More Tables

Example

Getting MySQL Row Count of All Tables in a Particular Database

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

MySQL regexp_instr() function

Learn MySQL Tutorial

You may also like