Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:
Calculate Index Sizes
mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES
WHERE table_schema LIKE 'database';
+------------------+
|Total Index Size |
+------------------+
|1.70 GB |
+------------------+
1 row in set (1.60 sec)
Calculate the Total Size of Stored Data
mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size'
FROM information_schema.TABLES
WHERE table_schema LIKE 'database';
+-----------------+
| Total Data Size |
+-----------------+
|3.01 GB |
+-----------------+
1 row in set (1.35 sec)
Per Table Sizes
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,2),'M') AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') AS 'Index Size' ,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') AS'Total'
FROM information_schema.TABLES
WHERE table_schema LIKE 'database';
Just replace database with the partial name of your database you need to analyze. Yes, I know, those wonderful Maatkit tools contains mk-find which can do the same thing, but then you won't learn about the information_schema database!! It's important to also know that running the above commands on a server that's very busy can result in slowing it down even more. The information schema tables are 'virtual tables' they don't exist like regular database tables. Just be aware, and if you need to collect this information on a busy database, make a backup copy of your Drupal database and move it to a development server.
