Determining MySQL Database Size

When working with MySQL, it's often necessary to ascertain the size of a specific database. One such scenario arises when a database named "v3" needs to be sized.

Query to Retrieve Database Size

The following SQL query can be executed to retrieve the size of the database in megabytes:

SELECT table_schema "DB Name",
        ROUND(SUM(data_length   index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema; 

Understanding the Query

• table_schema: This field represents the database name.
• SUM(data_length index_length): This calculates the total size of all tables' data and indexes in bytes.
• ROUND(... / 1024 / 1024, 1): This rounds the result to one decimal place and converts it from bytes to megabytes (MB).

Example Output

For the "v3" database, the query might return an output similar to:

 ---------- --------------- 
| DB Name   | DB Size in MB |
 ---------- --------------- 
| v3        |  28.5        |
 ---------- --------------- 

This indicates that the "v3" database is approximately 28.5 megabytes in size.