How to Get Record Count for All Tables in MySQL Database

In DBMS, counting records for all tables within a MySQL database is a fundamental requirement. Understanding the size and distribution of data across tables helps optimize database performance and provides insights into data utilization and growth patterns.

Row or record count means how many records are present in the database. Here we will discuss various methods to efficiently retrieve record counts from all tables within a MySQL database.

How to Get Record Counts for All Tables

The following methods can be used to get the record count for all tables in MySQL.

These methods differ in query complexity and execution time. INFORMATION_SCHEMA.TABLES with SUM function might be a complex query but have less execution time, whereas COUNT(*) and UNION are simple queries but need more execution time.

Demo MySQL Database

First, we’ve to create a Database with the help of the query below we’ll name it ‘demodb’.

CREATE DATABASE demodb;

We’ll now create a table named ‘gfg’ using the following code which defines the table structure with columns such as ‘Name,’ ‘Designation,’ and ‘Age,’ as Columns.

CREATE TABLE gfg 
(
    Name VARCHAR(50),
    Designation VARCHAR(50),
    Age INT
);

INSERT INTO gfg (Name, Designation, Age)
VALUES
    ('Bishal Paul', 'GFG Writer', 21),
    ('Charles', 'MERN Geek', 35),
    ('Priyanka', 'SDE', 26);

We’ll also create a second table named ‘article’ using the following code which defines the table structure with columns such as ‘Title,’ ‘Date,’ ‘Written By’ and ‘Age,’ as Columns.

CREATE TABLE article 
(
    Title VARCHAR(50),
    Date Date,
    WrittenBy INT,
    Age INT
);

INSERT INTO article (Title, Date, WrittenBy, Age)
VALUES
    ('How to get record counts for all tables', '2024-02-10', 'Bishal' , 22)

Output:

article table

Using INFORMATION_SCHEMA.TABLES with SUM function to Get Record Count

This approach involves retrieving the sum of the estimated row counts for all tables within the specified database (yourDatabaseName). It operates on the INFORMATION_SCHEMA.TABLES system view, which contains metadata about database tables. The TABLE_ROWS column in this view provides an approximate row count for each table and then aggregate function SUM gives total count of rows/records.

Syntax

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yourDatabaseName’;

Example:

The provided SQL query retrieves the sum of estimated row counts for all tables in the ‘demodb’ database using the INFORMATION_SCHEMA.TABLES view. Here’s an explanation of the query:

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'demodb';

Output:

No. of rows in all tables in database i.e. demodb

Explanation: The output of the query provides the estimated total number of rows across all tables in the ‘demodb’ database, offering a quick insight into the overall data volume.

*Note: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You’ll need to use COUNT(*) for exact counts (which is more expensive).

Using COUNT(*) to Get Record Count

In this method, use various subqueries, i.e., one subquery for each individual table record count.

Syntax:

SELECT   
    (SELECT COUNT(*) FROM 'your_table_name1') AS table1Count,   
    (SELECT COUNT(*) FROM ' your_table_name2') AS table2Count;  

Example:

The query calculates and displays the counts of records in the ‘article‘ and ‘gfg‘ tables. ‘articleCount‘ indicates the record count in the ‘article’ table, and ‘gfgCount‘ indicates the record count in the ‘gfg‘ table.

SELECT   
    (SELECT COUNT(*) FROM article) AS articleCount,   
    (SELECT COUNT(*) FROM gfg) AS gfgCount;  

Output:

No of rows of each table

Explanation: By executing the query, it retrieves and displays the counts of records in the ‘article‘ and ‘gfg‘ tables. The ‘articleCount’ is the count of records in the ‘article‘ table, and ‘gfgCount’ is the count of records in the ‘gfg‘ table.

Using UNION to Get Record Count

The UNION operation combines rows from both queries. The operator combines the result returned by the individual SELECT query.

Syntax:

SELECT 'table1' table_names, COUNT(*) AS row_count FROM ' table_name'
UNION 
SELECT 'table2', COUNT(*) FROM 'table_name';

Example

The query combines and displays the record counts for the ‘article’ and ‘gfg’ tables. It uses UNION to present the results with corresponding table names (‘article’ and ‘gfg’).

SELECT 'article' table_names, COUNT(*) AS row_count FROM article 
UNION 
SELECT 'gfg', COUNT(*) FROM gfg;

Output:

Rows Count of Each Table

Explanation: The output provides a unified result of record counts for both the ‘article‘ and ‘gfg‘ tables. Each row displays a table name (‘article’ or ‘gfg’) alongside the respective count of records in that table.

Conclusion

This guide explained three distinct approaches to count records in a MySQL database. Using the TABLE_ROWS attribute with the aggregate function SUM and employing a direct count of rows for each table. All approaches offer unique insights into the record counts in database.

The choice among them depends on the specific requirements of the analysis. If a quick estimate of the overall data volume is sufficient, Approach 1 provides a convenient solution. However, for detailed and accurate record counts at the table level, Approach 2 and Approach 3 offers a more comprehensive approach but at the cost of increased query complexity and execution time.