How to view mysql tablespaces and indexes: mysql Indexes
1. view the index (1) in GB
Select concat (ROUND (SUM (index_length)/(1024*1024*1024), 2), 'gb') AS 'total Index size' FROM information_schema.TABLES WHERE table_schema LIKE 'database ';
+ ------------------ +
| Total Index Size |
+ ------------------ +
| 1.70 GB |
+ ------------------ +
(2) The unit is MB.
Select concat (ROUND (SUM (index_length)/(1024*1024), 2), 'mb') AS 'total Index size' FROM information_schema.TABLES WHERE table_schema LIKE 'database ';
"Database" indicates the database you want to view.
2. View tablespaces
Select concat (ROUND (SUM (data_length)/(1024*1024*1024), 2), 'gb') AS 'total Data size'
FROM information_schema.TABLES WHERE table_schema LIKE 'database ';
+ ----------------- +
| Total Data Size |
+ ----------------- +
| 3.01 GB |
+ ----------------- +
3. View information about all tables in the database
Select concat (table_schema, '.', table_name) AS 'table name ',
CONCAT (ROUND (table_rows/Rows), 'M') AS 'number of Rows ',
CONCAT (ROUND (data_length/(1024*1024*1024), 2), 'G') AS 'data size ',
CONCAT (ROUND (index_length/(1024*1024*1024), 2), 'G') AS 'index size', CONCAT (ROUND (data_length + index_length) /(1024*1024*1024), 2), 'G') AS 'Total' FROM information_schema.TABLES WHERE table_schema LIKE 'database ';