Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 553
user784637 Avatar asked Dec 02 '11 22:12

user784637


1 Answers

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

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

like image 76
Bill Karwin Avatar answered Sep 20 '22 23:09

Bill Karwin