Home » Learn – MySQL ROW COUNT

Learn – MySQL ROW COUNT

  • by

Learn – MySQL ROW COUNT

How can we find out how many rows there are in a MySQL table?
The row count refers to the number of records in the database. It’s a crucial action for determining and monitoring the table’s growth during development and operations. The MySQL COUNT() method is the most basic and often used function for returning the number of rows in a table. It’s a summarise function that operates on the complete set of records and returns a single output.

The MySQL COUNT() method can be used to perform the following tasks:

To get the total number of records in a table.
The number of rows returned by a query or an aliased table.

To count how many non-null values there are in a column.
To count how many distinct or unique values there are in a column.

To get the row count of a single table in MySQL, use the following syntax:

SELECT COUNT (*)
FROM table_name
[WHERE conditions];

mysql > SELECT COUNT(*) AS Row_Count FROM employee;

mysql > SELECT
(SELECT COUNT(*) FROM employee) As Table1Count,
(SELECT COUNT(*) FROM orders) As Table2Count;

mysql > SELECT 'employee' table_name1, COUNT(*) AS row1 FROM employee

UNION SELECT ‘orders’ table_name2, COUNT(*) AS row2 FROM orders;

mysql > SELECT table_name FROM information_schema.tables
WHERE table_schema = 'testdb' AND table_type = 'BASE TABLE';

mysql > SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'testdb'
ORDER BY table_name;

Sometimes this query does not returns accurate result. It is because the actual row count in the tables and the row count in the information_schema are not synchronized. We can avoid this inaccurate result by running the ANALYZE TABLE statement before querying row count from information_schema database.


mysql > ANALYZE TABLE table_name