MySQL Row Count: How to get the number of lines in MySQL.

Preamble

In this post you will learn how to get the number of MySQL lines in a database.

Getting the number of MySQL rows in one table

To get the number of rows per table, you use the COUNT(*) operator in SELECT as follows:

SELECT
COUNT(*)
FROM
table_name;

For example, to get the number of rows in the andreyex table in a database example, use the following operator:

SELECT
COUNT(*)
FROM
andrex;

+ ---------- +
| COUNT(*) |
+ ---------- +
| 35 |
+ ---------- +
1 row in set (0.01 sec)

Getting the number of MySQL rows in two or more tables

To get the number of rows in several tables, you use the UNION operator to combine sets of results returned by each individual SELECT operator.

For example, to get the number of rows in a table and the number of rows in a single query, use the following instructions.

SELECT
'andrex' tablename,
COUNT(*) rows
FROM
andrex
UNION
SELECT
"trainings" tablename,
COUNT(*) rows
FROM
I'm sorry;

| tablename | rows |

| andrex | 34 |
| | 451 |

2 rows in set (0.01 sec)

Getting the number of MySQL rows of all tables in a particular database

To get the number of rows of all tables in a particular database, such as classicmodels, you use the following steps:

• First, get all table names in the database
• Second, create a SQL statement that includes all SELECT COUNT(*) FROM table_name operators for all tables separated by UNION.
• Third, execute a SQL statement using the prepared statement

First, to get all the names of the database tables, you query the following from the information_schema database:

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

+ -------------- +
| TABLE_NAME |
+ -------------- +
| andrex |
|
| sites |
+ -------------- +
3 rows in set (0.02 sec)

Second, to build the SQL operator, we use GROUP_CONCAT and CONCAT functions as follows:

SELECT
CONCAT(GROUP_CONCAT(CONCAT('SELECT ''',
table_name,
'' table_name,COUNT(*) rows FROM ',
table_name)
' UNION '),
' ORDER BY table_name')
INTO @sql
FROM
table_list;

This query contains a list of table_list table names, which is the result of the query at the first stage.

The next query uses the first query as a derived table and returns the SQL instruction as a string.

SELECT
CONCAT(GROUP_CONCAT(CONCAT('SELECT ''',
table_name,
'' table_name,COUNT(*) rows FROM ',
table_name)
' 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

If you use MySQL 8.0+, you can use MySQL CTE (generic table expression) instead of a derived table:

WITH table_list AS (
SELECT
table_name
FROM information_schema.tables
WHERE table_schema = 'classicmodels' AND
table_type = 'BASE TABLE'
)
SELECT CONCAT(
GROUP_CONCAT(CONCAT("SELECT '",table_name," table_name,COUNT(*) rows FROM ",table_name) SEPARATOR " UNION "),
' ORDER BY table_name'.
)
INTO @sql
FROM table_list;

Third, you execute the @sql operator using the prepared operator as follows:

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

Getting the number of MySQL rows of all tables in a database by one query

A quick way to get the number of rows of all tables in the database is to query data from the information_schema database directly:

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

This method is sometimes not accurate because the number of rows in information_schema and the actual number of rows in tables are not synchronized. To avoid this, you must follow the ANALYZE TABLE instruction before requesting the number of rows in the information_schema database.

ANALYZE TABLE table_name,...;

In this article you learned about different ways to get the number of rows of one or more tables in a MySQL database.

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.