If you’ve ever needed to know how the data and index percentages per table engine were laid out on your MySQL server, but didn’t have the time to write out a query… here it is!

select (select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(POW(1024,3)) as total_size from tables) as total_size_gb, (select sum(INDEX_LENGTH)/(POW(1024,3)) as index_size from tables) as total_index_gb, (select sum(DATA_LENGTH)/(POW(1024,3)) as data_size from tables) as total_data_gb, (select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables) as perc_index, (select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables) as perc_data, (select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='innodb') as innodb_perc_index, (select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='innodb') as innodb_perc_data, (select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='myisam') as myisam_perc_index, (select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='myisam') as myisam_perc_data, (select (select sum(INDEX_LENGTH) from tables where ENGINE='innodb') / (select sum(INDEX_LENGTH) from tables))*100 as perc_total_index_innodb, (select (select sum(DATA_LENGTH) from tables where ENGINE='innodb') / (select sum(DATA_LENGTH) from tables))*100 as perc_total_data_innodb, (select (select sum(INDEX_LENGTH) from tables where ENGINE='myisam') / (select sum(INDEX_LENGTH) from tables))*100 as perc_total_index_myisam, (select (select sum(DATA_LENGTH) from tables where ENGINE='myisam') / (select sum(DATA_LENGTH) from tables))*100 as perc_total_data_myisam from tables limit 1\G

This will output something along the lines of:

total_size_gb: 2.7505537783727 total_index_gb: 0.018660545349121 total_data_gb: 2.7318932330236 perc_index: 0.6784 perc_data: 99.3216 innodb_perc_index: 0.6502 innodb_perc_data: 99.3498 myisam_perc_index: 17.5120 myisam_perc_data: 82.4880 perc_total_index_innodb: 95.6713 perc_total_data_innodb: 99.8607 perc_total_index_myisam: 4.3287 perc_total_data_myisam: 0.1393

Good one

Hi!

while I think the idea is nice, the query as you propose it does a lot of unnecessary work. Normally I am not big fan of premature optimization, but from experience I learned to avoid or at least be very careful with accessing MySQL’s information_schema tables multiple times in the same statement.

For example, after repeatedly running your statement (excluding the first run) I find it takes about 2min44sec on average to run it. With a slight modification to access information_schema.tables only once:

select (data_size + index_size) / gb as total_size

, index_size / gb as index_size

, data_size / gb as data_size

, index_size / (data_size + index_size) * 100 as perc_index

, data_size / (data_size + index_size) * 100 as perc_data

, innodb_index_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_index

, innodb_data_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_data

, myisam_index_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_index

, myisam_data_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_data

, innodb_index_size / index_size * 100 as innodb_perc_total_index

, innodb_data_size / data_size * 100 as innodb_perc_total_data

, myisam_index_size / index_size * 100 as myisam_perc_total_index

, myisam_data_size / data_size * 100 as myisam_perc_total_data

from (

select sum(data_length) data_size

, sum(index_length) index_size

, sum(if(engine = ‘innodb’, data_length, 0)) as innodb_data_size

, sum(if(engine = ‘innodb’, index_length, 0)) as innodb_index_size

, sum(if(engine = ‘myisam’, data_length, 0)) as myisam_data_size

, sum(if(engine = ‘myisam’, index_length, 0)) as myisam_index_size

, pow(1024, 3) gb

from tables

) a

I find an average execution time of slightly less than 10 seconds.

Roland, thanks – that does run quicker!

Brilliant one.