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

Question

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.

score 29 · Accepted Answer · edited May 23 '17 at 12:10

29

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

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

edited May 23 '17 at 12:10

Community

1
1

answered Dec 02 '11 at 22:23

Bill Karwin

538,548
86
673
828

+1 for the answer, and +1 for the answer on single quotes vs. double quotes – user784637 Dec 02 '11 at 22:28

elrobis · Answer 2 · 2021-09-28T13:45:46.583

This was a helpful thread. This slight variation on the OP's second recipe returns tables only (no views) and orders the returned dataset by table size---largest-to-smallest:

MySQL:

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 = 'yourSchemaName' and TABLE_TYPE='BASE TABLE' 
ORDER BY data_length DESC;

PostGRES: I needed to do the same thing in PostGRES today, and borrowing some help from this answer (and another one I lost after closing the browser tab), this is what I ended up with. Adding it here in case it's useful for anyone else.

[Edit 9.28.2021: I was getting an error from an RDS instance, basically 'pg_total_relation_size does not exist', and the solution was to cast t.tbl::text in the initial portion of the query.]

SELECT 
  t.tbl table_name,
  ct.reltuples row_count,
  pg_total_relation_size(t.tbl::text) size,
  pg_size_pretty(pg_total_relation_size(t.tbl::text)) pretty_size
FROM (
  SELECT 
    table_name tbl
    FROM information_schema.tables
    WHERE 
      table_schema = 'public'
        AND table_type = 'BASE TABLE'
) t
join (
  SELECT 
    relname, reltuples
  FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE 
    nspname NOT IN ('pg_catalog', 'information_schema') 
      AND relkind='r' 
) ct 
on t.tbl = ct.relname
order by size desc ;

score 12 · Answer 3 · answered Dec 02 '11 at 22:23

12

I Think this is better .. http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

show table status from database;

answered Dec 02 '11 at 22:23

A downside to this approach is that it will not accept an ORDER BY clause. :/ – elrobis Jun 22 '12 at 17:10

score 3 · Answer 4 · answered Oct 23 '12 at 12:50

Here is a Query I use to give the size of each table in a database.

SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
   ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
   SUM(sys.length) AS Byte_Length
FROM sysobjects sob, syscolumns sys
WHERE sob.xtype='u' AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE

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

4 Answers4