user784637 user784637 - 3 months ago 7
MySQL Question

Query to determine the size of tables in a database? (mysql)

The website "How to calculate the MySQL database size" gives two queries:

Determine sizes of all databases

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;


Determine size of all tables in a database

SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "schema_name";


The first query works correctly, but the second query doesn't produce a result set. It just shows the names of the fields without any rows. How can I modify the 2nd query to correctly show the size of sizes of my tables in my database.

Answer

Replace "schema_name" with the name of one of your databases.

And use single-quotes for string literals, not double-quotes.