Most Efficient Way to Get Table Row Count in SQL

1. Overview

As a database administrator, counting the number of rows is a fundamental task in SQL for purposes like tracking table sizes, assessing data distribution, or monitoring performance. However, we may sometimes find it difficult or slow to perform this operation efficiently when working with large databases.

In this tutorial, we’ll discuss different efficient approaches to retrieve the table row count for three major databases: SQL Server, PostgreSQL, and MySQL.

To illustrate, we’ll use the Baeldung University database schema as a working example.

2. Using COUNT(*)

Of course, we can always use the COUNT(*) query to display the table row count, which applies to all databases. This method provides exact and accurate results, requiring no database-specific knowledge.

However, the performance of COUNT(*) degrades on large datasets since it requires a complete table scan.

Still, for completeness, let’s take a look at the syntax of a simple COUNT(*) query:

SELECT COUNT(*) FROM table_name

Above, we replace the table_name with the actual table name.

3. SQL Server Lightweight Check

In SQL Server, we can use the dynamic management views to get a fast and approximate row count without scanning the entire table. This lightweight approach is ideal for database monitoring, where perfect accuracy isn’t required.

To demonstrate, let’s try counting the number of rows in the student table:

SELECT SUM(row_count) AS approx_rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.student')
  AND index_id IN (0, 1);

Upon running the query, we see the respective output:

Consequently, we can see that the student table contains approximately 25 rows.

Now, for the exact row count, let’s run the COUNT(*) query on the student table:

SELECT COUNT(*) FROM student

This query returns the same output:

In both cases, we can see that the number of rows is 25, which means the results are accurate and consistent. Nevertheless, if there are frequent inserts or deletes, it’s important to note that the dynamic management views may not reflect recent changes due to the reliance on cached metadata.

4. PostgreSQL pg_class

In PostgreSQL, we can utilize the system catalog, such as pg_class, which enables us to obtain the approximate row count extremely quickly. This approach is highly efficient for large tables, delivering instant results without table scanning.

Before proceeding, we should analyze the table to ensure that the statistics are up to date. For instance, let’s analyze the Department table:

# ANALYZE public.Department;
ANALYZE

Afterward, we can use a query to show the estimated row count:

university=# SELECT reltuples::BIGINT AS estimate
university-# FROM pg_class
university-# WHERE oid = 'public.Department'::regclass;
 estimate
----------
        5
(1 row)

As a result, we see that the Department table has 5 rows.

To verify, let’s execute the COUNT(*) query on the same table:

# SELECT COUNT(*) FROM Department;
 count
-------
     5
(1 row)

At this moment, we can observe that the number of rows is also 5 for the Department table, showing that the estimate is accurate. However, the pg_class statistics depend upon the last time the table was analyzed; if we re-execute the pg_class query without running the ANALYZE statement after significant changes, the estimate may not reflect the current data.

5. MySQL Table Status

In MySQL, we can use the SHOW TABLE STATUS command to retrieve table information, including row counts. This method is particularly effective for obtaining quick estimates without performing a full table scan.

For example, let’s examine the course table to get the row count. For this purpose, we can execute a simple query:

SHOW TABLE STATUS LIKE 'course';

Upon execution, we see an output that contains different metadata:

In this case, we can see that there are approximately 81 rows in the table.

Again, to confirm the exact row count, let’s run the COUNT(*) query on the course table:

SELECT COUNT(*) FROM course

Below, we see this output for the above query:

In this case, we can see that both approaches return the same number of rows, proving the estimate is accurate.

As an alternative approach, we can use the Information Schema query to check the approximate row count:

SELECT table_rows
FROM information_schema.tables
WHERE table_schema = 'university'
  AND table_name = 'course';

This query should return similar results.

Nonetheless, it’s important to understand that for InnoDB tables, the row count from the SHOW TABLE STATUS or Information Schema is approximate, but for MyISAM tables, the count is typically exact.

6. Conclusion

In this article, we learned efficient methods for counting table rows in SQL Server, PostgreSQL, and MySQL.

First, we saw the universal COUNT(*) approach to get exact and accurate results. Then, we discussed dynamic management views in SQL Server, pg_class in PostgreSQL, and the SHOW TABLE STATUS command in MySQL to get the approximate row count.