How to get the sizes of the tables of a MySQL database?

 

You can get the sizes of tables in a MySQL database by using SQL queries. Specifically, you can query the information_schema database, which contains metadata about all the tables in your MySQL server. Here's how you can do it:

Example SQL Query:

sql

SELECT
    table_name AS "Table",
    round(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM
    information_schema.tables
WHERE
    table_schema = 'your_database_name'
ORDER BY
    (data_length + index_length) DESC;

In this query:

• table_name is the name of the table.
• data_length represents the data size of the table in bytes.
• index_length represents the index size of the table in bytes.
• We calculate the total size in megabytes (MB) by adding data_length and index_length and dividing by 1024 twice (to convert from bytes to kilobytes and then to megabytes).
• table_schema should be replaced with the name of your database.

Replace 'your_database_name' with the name of your MySQL database that you want to analyze. When you run this query, it will return a result set with two columns: "Table" and "Size (MB)," listing the tables in the specified database along with their sizes in megabytes.

Example Usage in MySQL Command Line:

1. Open your terminal or command prompt.

2. Log in to your MySQL server using the mysql command-line tool. Replace username, password, and your_database_name with your actual credentials and database name:

   bash

mysql -u username -p -D your_database_name

Once you are logged in, you can execute the SQL query:

sql

3. SELECT
       table_name AS "Table",
       round(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
   FROM
       information_schema.tables
   WHERE
       table_schema = 'your_database_name'
   ORDER BY
       (data_length + index_length) DESC;
   

4. The result will display a list of tables in the specified database along with their respective sizes in megabytes.

This query provides valuable information about the sizes of tables in your MySQL database, which can be useful for database optimization and monitoring purposes.