Sometimes we need to know the space occupied by a table in the MySQL database because of work needs. How can this problem be solved? In fact, it is very simple. Have you noticed that all databases will come with a information_schema database that seems to have never been used before? We will not talk about the usage of this database, but we need to check the space occupied by a table through this database.
First, open the information_schema database of MySQL and find the TABLES Table in this database. The main fields of this table are:
TABLE_SCHEMA: Database Name
TABLE_NAME: Table Name
ENGINE: The storage ENGINE used
TABLES_ROWS: number of records
DATA_LENGTH: data size
INDEX_LENGTH: Index size
Of course, there are other fields. For details, refer to the MySQL official manual.
If you need to know the size of the space occupied by a table, the result is the data size + index size in the above table fields. You can also use the following SQL statement to query:
SELECT TABLE_NAME, DATA_LENGTH + INDEX_LENGTH, TABLE_ROWS from tables where TABLE_SCHEMA = 'database name' AND TABLE_NAME = 'table name ';
Articles you may be interested in
- Use MySQL Slow Log to analyze the high CPU usage of MySQL
- Note the following when querying strings with single quotes and inserting strings with single quotes in Mysql:
- Usage example of mysql union Query
- Run the doscommand in Windows to view the program that occupies port x.
- Js address bar special effects (display the size of all images with links on the page and view the height of the current browser)
- Mysql Command for querying table Indexes
- Summary of Solutions to slow Mysql response and high CPU usage
- Conversion of date and unix timestamps in php and mysql