Hi to everyone someone know how to get an estimate about the size of one mysql table? I mean I am planning to make a backup of all the mysql's table on all my server but I'd like to know how big should be every dump table without make the physical dump. There is some command which permit to do this one? I am using mysql 5.1 on debian 6 Cheers

2 Answers 2


As seen here, you can run these queries on your database (stole from comments on the page, then tweaked).

  • By database:

SELECT table_schema 'database', concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size FROM information_schema.TABLES WHERE ENGINE=('MyISAM' || 'InnoDB' ) GROUP BY table_schema;

  • By table:

SELECT concat( table_schema, '.', table_name ) table_name, concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size FROM information_schema.TABLES ORDER BY ( data_length + index_length ) DESC;

  • perfect is the exactly what I was looking for ;)
    – marcodv
    Jul 30, 2012 at 13:32

Since a mysqldump is a logical representation of the database, you will only find actual data. Indexes are never stored in a mysqldump, but the table definition will dictate how to populate indexes.

It is hard to pickoff an exact number for a mysqldump because of what is written to a mysqldump:

  • Extended INSERT commands with dozens,hundreds, or thousands of rows
    • dozens or hundreds of parentheses and commas per INSERT
  • Table creation commands
  • Directives for
    • Disabling Foreign Keys and Unique Keys for Duration of mysqldump
    • Recording Log File and Position of the mysqldump
    • Disabling/Enabling Indexes (MyISAM only)
    • Locking a Whole Table for Writes
    • Saving/Restoring Global Variables

What you can gauge as a factor to go by is just the sum of the data_lengths:

SELECT SUM(data_length)/POWER(1024,2) TotalDB_MB
FROM information_schema.tables;

To see the sum by database and grand total:

SELECT table_schema,SUM(data_length)/POWER(1024,2) DB_MB
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
GROUP BY table_schema

To see every table and databases subtotaled:

SELECT table_schema,table_name,SUM(data_length)/POWER(1024,2) DB_MB
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
GROUP BY table_schema,table_name

You can use the following

  • POWER(1024,1) reports in KB
  • POWER(1024,2) reports in MB
  • POWER(1024,3) reports in GB
  • POWER(1024,4) reports in TB
  • POWER(1024,5) reports in PB
    • That's PetaBytes
    • If you have to use this one, I charge reasonable consulting rates

You could use this Formula

  • NOC = Number of Columns Per Table
  • NumCommas = NOC - 1
  • NumQuotes = NumCharacterColumnsInTable X 2
  • NumRows = Number of Rows Per Table
  • NumParens = NumRows X 2
  • CharactersForSQLPerTable = NumRows X (NumCommas + NumQuotes) + NumParens

Final Formula for Approximate Size of a mysqldump

  • CharactersForSQLPerTable X NumberOfTables
  • + Characters for "INSERT INTO tblname VALUES "
  • + TotalDB_MB

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .