Determine which table takes most disk space in MySQL
W开发者_Python百科hat is the easiest way to determine which table takes most disk space ?
A handicap: I have no MySQL server, only the file with all data (dump.sql)
You may want to download 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;
If you're on linux, you can scan the dump file for the longest line, which may (or may not!) be the largest table depending on indexes, structure etc, but it's going to be a decent guess if you can't get a server up.
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 line. This may not work if you have, for example, multiple inserts per table in your dump file (with --extended-insert
or --opt
options).
A quick solution might be to do something like that 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 functionally of the linux command line on a windows system)
This command will filter out the inserts statements of the dump for a specific table and print out the total bytes of those filtered insert statements. Of course, the size of the insert statements isn't the same as the size of the stored rows in the table, but it might be a good approximation if you only need to decide which table is (probably) the largest.
精彩评论