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.

• Using INFORMATION_SCHEMA.TABLES with SUM function to Get Record Count
• Using COUNT(*) to Get Record Count
• Using UNION to Get Record Count

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 the aggregate function SUM gives the 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 the 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 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 the 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 offer a more comprehensive approach but at the cost of increased query complexity and execution time.

FAQs on Counting Records in MySQL Database

Why is it important to count records in all tables within a MySQL database?

Counting records helps optimize database performance, provides insights into data utilization and growth patterns, and aids in capacity planning.

What are the methods to get record counts for all tables in MySQL?

The methods include using INFORMATION_SCHEMA.TABLES with the SUM function, using COUNT(*) with subqueries for each table, and using UNION to combine counts from multiple tables.

How can I get an estimated row count using INFORMATION_SCHEMA.TABLES?

You can use the following query:

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

This provides an approximate total row count for all tables in the specified database.