Determine which table occupies most of the disk space in MySQL

Question

What is the easiest way to determine which table takes up the most disk space?

Handicap: I do not have a MySQL server, only a file with all the data (dump.sql)

Answer 1

You may want to download the MySQL server , install it on your localhost, import the dump file , and then use:

SELECT table_schema, table_name, data_length, index_length FROM information_schema.tables ORDER BY data_length DESC; 

Answer 2

If you are working on Linux, you can scan the dump file for the longest row, which may (or may not be!) Be the largest table depending on indexes, structure, etc., but it will be a worthy guess if you do not can get a server.

 awk ' { if ( length > L ) { L=length ;s=$0 } }END{ print L,"\""s"\"" }' /root/sql/_common.all.301009.sql | cut -c 1-100 

This will show you the first 100 characters of the longest string. This may not work if, for example, you have several attachments for each table in the dump file (with the --extended-insert or --opt ).

Answer 3

A quick solution might be to do something similar for each table in the dump:

 cat dump.sql | grep -i '^INSERT INTO `table1`' | wc -c 

(I hope you are using Linux, otherwise you can install Cygwin to get some of the linux command line functionality on a Windows system)

This command will filter out dump insert instructions for a particular table and print the common bytes of these filtered paste statements. Of course, the size of the insert statements does not match the size of the rows stored in the table, but it can be a good approximation if you only need to decide which table is (possibly) the largest.