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:

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;

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: