MySQL Query To Find The Tables Using Most Space in Database

Problem:
How to get the table list which is using most space in MySQL/MariaDB database?

Solution:
If you want to get tables list which is using most space in MySQL/MariaDB database. Follow these steps and get the result

Step 1. Select the database

Step 2. Copy and Paste the below query

SQL Query:

[code] SELECT CONCAT(table_schema, ‘.’, table_name) Database_TableName, CONCAT(ROUND(table_rows / 1000000, 2), ‘M’) Table_Rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), ‘GB’) Data_Length, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), ‘GB’) Index_Length, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), ‘GB’) Total_Data_Length, ROUND(index_length / data_length, 2) Index_Data_Length FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 20; [/code]

Step 3. Get the list of table list with Database_TableName, Table_Rows, Data_Length, Index_Length, Total_Data_Length, and Index_Data_Length. An example image is given below: