Find the largest tables in MySQL

Find the largest tables in MySQL Server

If you find yourself regularly needing to find or report on the largest tables in your MySQL database, the following SQL statement should do the trick:

SELECT table_schema as database_name,
       table_name,
       table_rows as `Row Count`,
       ROUND( (data_length + index_length) / 1024 / 1024, 2) 
       as `Total Size (Mb)`,
       ROUND( (data_length) / 1024 / 1024, 2)  as `Data Size (Mb)`,
       ROUND( (index_length) / 1024 / 1024, 2)  as `Index Size (Mb)`
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE'
   AND table_schema NOT IN ('information_schema', 
                            'performance_schema', 
                            'mysql',
                            'sys')
ORDER BY `Total Size (Mb)` DESC

The Breakdown

Below is a breakdown of the fields being returned:

Row Count: The number of rows in the given table.
Total Size (Mb): The total size of the table (data + indexes).
Data Size (Mb): The total size of the data elements only.
Index Size (Mb): The total size of the indexes only.

If you want to filter by specific database, you can add the following to your where clause:

AND table_schema = 'my_database_name'

Or simply the one table you are interested in: