How to view MySQL tables by size

1 post / 0 new
#1 Sun, 2015-03-29 18:47
Offline
Joined: 2 years 1 week ago

How to view MySQL tables by size


I found a useful query to see MySQL tables by size:

  1. SELECT CONCAT(table_schema, '.', table_name),
  2. CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
  3. CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
  4. CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
  5. CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
  6. ROUND(index_length / data_length, 2) idxfrac
  7. FROM information_schema.TABLES
  8. ORDER BY data_length + index_length DESC
  9. LIMIT 10;

Example output:

MySQL table size

More info:
http://www.percona.com/blog/2008/02/04/finding-out-largest-tables-on-mys...