Determine which table takes most disk space in MySQL

Question

What 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)

Answer 1

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;

Answer 2

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).