How to query table and index size in MySQL

Querying MySQL table size and index size can be achieved through the tables table in the system library INFORMATION_SCHEMA.

Some of the fields used in this table are:

Table_schema: Database name
TABLE_NAME: Table name
Engine: the storage engine used
Tables_rows: Number of record lines
Data_length: Table Size
Index_length: Index size

1, you can query the database data row number, table space, index space, as below, replace [database name] with your own database name.

SELECT Table_schema as ' library name ',
CONCAT (ROUND (table_rows/10000, 2), ' million rows ') as ' number of rows ',
CONCAT (ROUND (SUM (data_length)/(1024*1024*1024), 2), ' GB ') as ' table space ',
CONCAT (ROUND (SUM (index_length)/(1024*1024*1024), 2), ' GB ') as ' index space ',
CONCAT (ROUND (SUM (data_length+index_length)/(1024*1024*1024), 2), ' GB ') as ' total space '
From INFORMATION_SCHEMA. TABLES WHERE table_schema = [database name];

2, of course, can not summarize, query the situation of the table under analysis, such as, the [database name] to replace your own database name can be.

SELECT table_name as ' table name ',
CONCAT (ROUND (table_rows/10000, 2), ' million rows ') as ' number of rows ',
CONCAT (ROUND (data_length/(1024*1024*1024), 2), ' GB ') as ' table space ',
CONCAT (ROUND (index_length/(1024*1024*1024), 2), ' GB ') as ' index space ',
CONCAT (ROUND ((data_length+index_length)/(1024*1024*1024), 2), ' GB ') as ' total space '
From INFORMATION_SCHEMA. TABLES WHERE table_schema = [database name] ORDER by Table_rows DESC;

How to query table and index size in MySQL