Home >Database >Mysql Tutorial >How to query the data volume of a table in mysql

How to query the data volume of a table in mysql

WBOY
WBOYOriginal
2022-05-16 10:39:2421901browse

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.

How to query the data volume of a table in mysql

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn