How Can I Efficiently Get the Total Row Count for All Tables in a MySQL Database?

Efficiently obtain the number of rows in all tables of the MySQL database

Overview

Getting the number of rows for each table in a MySQL database is a common task. The traditional approach is to perform time-consuming SELECT COUNT() queries against each table. However, MySQL's INFORMATION_SCHEMA table provides a more efficient solution.

Use INFORMATION_SCHEMA to get the number of table rows

To quickly get the total number of rows of all tables in a specified database without executing multiple queries, you can use the following statement:

<code class="language-sql">SELECT SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '{your_db}';</code>

Replace {your_db} with the name of the target database.

A note on accuracy

Although this method estimates the total number of rows quickly and efficiently, it should be noted that for InnoDB tables, the number of rows is only an approximation for SQL optimization. If an exact count is required, a COUNT(*) query should be performed on each table, but this will slightly reduce performance.

The above is the detailed content of How Can I Efficiently Get the Total Row Count for All Tables in a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!