Search

Get Row Count in MySQL 
Get Row Count in MySQL 

Get Row Count in MySQL 

show you various ways to get MySQL row count in the MySQL database.

How to Get Row Count in MySQL

This guide shows you how to find the number of rows in one or more tables within a MySQL database. There are multiple ways to achieve this depending on what you need.

1. Get Row Count for a Single Table

Use the COUNT(*) function in a SELECT query to count the rows in a table.

Syntax:

SELECT COUNT(*) FROM table_name;

Example: To count rows in the customers table:

SELECT COUNT(*) FROM customers

Output Example:

+----------+
| COUNT(*) |
+----------+
|      122 |
+----------+

2. Get Row Count for Multiple Tables

Use the UNION operator to combine row counts from multiple tables.

Syntax:


SELECT 'table_name_1' tablename, COUNT(*) 'rows' FROM table_name_1
UNION
SELECT 'table_name_2' tablename, COUNT(*) 'rows' FROM table_name_2;

Example: To count rows in customers and orders tables:

SELECT 'customers' tablename, COUNT(*) 'rows' FROM customers
UNION
SELECT 'orders' tablename, COUNT(*) 'rows' FROM orders;

Output Example:


+-----------+------+
| tablename | rows |
+-----------+------+
| customers |  122 |
| orders    |  326 |
+-----------+------+

3. Get Row Count for All Tables in a Database

To count rows for every table in a specific database (e.g., classicmodels):

Step 1: Get Table Names

Query the information_schema database to retrieve all table names in your database.

Syntax:


SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_database_name' AND table_type = 'BASE TABLE';

Example:


SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'classicmodels' AND table_type = 'BASE TABLE';

Output Example:

plaintext
Copy code
+--------------+
| TABLE_NAME   |
+--------------+
| customers    |
| employees    |
| offices      |
| orderdetails |
| orders       |
| payments     |
| productlines |
| products     |
+--------------+

Step 2: Construct a Combined Query

Use GROUP_CONCAT to build a query that counts rows for all tables.

Syntax:

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 = 'your_database_name' AND table_type = 'BASE TABLE') table_list;

Example:

sql
Copy code
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 = 'classicmodels' AND table_type = 'BASE TABLE') table_list;

Step 3: Execute the Query

Run the constructed SQL query using a prepared statement.

Syntax:

sql
Copy code
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Example:

sql
Copy code
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

4. Quick Method: Query information_schema for Row Counts

You can query information_schema.tables directly to get row counts for all tables in a database.

Syntax:

sql
Copy code
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY table_name;

Example:

sql
Copy code
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'classicmodels'
ORDER BY table_name;

Output Example:

plaintext
Copy code
+--------------+------------+
| table_name   | table_rows |
+--------------+------------+
| customers    |        122 |
| employees    |         23 |
| offices      |          7 |
| ...          |        ... |
+--------------+------------+

Note: This method may not be 100% accurate since information_schema might not sync row counts with the actual data. To ensure accuracy, you can run the ANALYZE TABLE statement before the query.

Syntax:

sql
Copy code
ANALYZE TABLE table_name;

Example:

sql
Copy code
ANALYZE TABLE customers, orders;

Summary of Methods:

  1. Use COUNT(*) for a single table.
  2. Use UNION for multiple tables.
  3. Use a prepared statement for all tables in a database.
  4. Use information_schema.tables for a quick (but less accurate) row count.

These techniques provide flexibility depending on your specific needs.