Advanced Search

MySQL biggest tables and databases

/ Published in: MySQL
Save to your folder(s)
http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/
http://forums.mysql.com/read.php?108,201578,201578

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SELECT table_schema "Data Base Name",
  2. sum( data_length + index_length ) / 1024 /
  3. 1024 "Data Base Size in MB",
  4. sum( data_free )/ 1024 / 1024 "Free Space in MB"
  5. FROM information_schema.TABLES
  6. GROUP BY table_schema ; 
  7.  
  8.  
  9. SELECT CONCAT(table_schema, '.', table_name),
  10.        CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
  11.        CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
  12.        CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
  13.        CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
  14.        ROUND(index_length / data_length, 2)                                           idxfrac
  15. FROM   information_schema.TABLES
  16. ORDER  BY data_length + index_length DESC
  17. LIMIT  10;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.