Check size of tables and indexes for all storage engines

by | Jul 27, 2017 | Usefull scripts

source: http://www.speedemy.com/mysql/17-key-mysql-config-file-settings/innodb_buffer_pool_size/

Totals for engines:

SELECT engine,
  count(*) as TABLES,
  concat(round(sum(table_rows)/1000000,2),'M') rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema', 'sys')
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC LIMIT 10;

 

Totals for database and engine:

SELECT 
  table_schema as database_name,
  engine,
  count(*) as TABLES,
  concat(round(sum(table_rows)/1000000,2),'M') rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema', 'sys')
GROUP BY table_schema, engine
ORDER BY table_schema, sum(data_length+index_length) DESC LIMIT 10;