How to query the data volume of a table in mysql

Mysql query table data volume method: 1. Use the "SELECT COUNT(*) FROM table name" statement to query, COUNT can return the number of retrieved data; 2. Use "SELECT SQL_CALC_FOUND_ROWS 1 FROM table name limit 1 "Statement query.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to query the data volume of a table in mysql

1. The most commonly used

SELECT COUNT(*) FROM 表名;

is accurate, but if the data volume is large (more than 1 million), it will be slow.

2. I found a method online, which is said to be faster than count(*). The self-test has not verified it.

SELECT SQL_CALC_FOUND_ROWS 1 FROM 表名 limit 1;
SELECT found_rows() AS rowcount;

Both of the above have disadvantages. Only one table can be queried at a time.

3. Query the data volume of all tables in the current database

SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = (SELECT database())
ORDER BY TABLE_ROWS DESC;

TABLE_ROWS is the table data volume, but you will find that the value obtained by executing select count(*) is different!

Reason:

By default, when mysql adds or deletes a table, it will not automatically update the table_rows field of the tables table in the information_schema library. Searching the Internet, I found that: only 10% It will be automatically collected only when the number of rows changes (to be verified)

Recommended learning: mysql video tutorial

The above is the detailed content of How to query the data volume of a table in mysql. For more information, please follow other related articles on the PHP Chinese website!