data_size_per_table

[GoogleCodeExporter]

What component are you suggesting? (view, table, function, procedure)
A view showing disk usage per table.

What will this new component do?
Show how much disk space is used per database & table.

Characteristics of the component? (e.g. NO SQL for stored function)
View

Dependencies for component?
None

Compatibility with what kind of MySQL version/distribution?
Compatible

OS specific issues?
None.

Do you expect this to be a heavyweight operation? Why?
No.

Anything else?
CREATE ALGORITHM=TEMPTABLE VIEW `data_size_per_table` AS select 
`information_schema`.`TABLES`.`TABLE_SCHEMA` AS 
`TABLE_SCHEMA`,`information_schema`.`TABLES`.`TABLE_NAME` AS 
`TABLE_NAME`,round((`information_schema`.`TABLES`.`DATA_LENGTH` / (1024 * 
1024)),0) AS `DATA_LENGTH`,round((`information_schema`.`TABLES`.`INDEX_LENGTH` 
/ (1024 * 1024)),0) AS 
`INDEX_LENGTH`,round(((`information_schema`.`TABLES`.`DATA_LENGTH` / (1024 * 
1024)) + (`information_schema`.`TABLES`.`INDEX_LENGTH` / (1024 * 1024))),0) AS 
`TABLE_DISK_SPACE`,ifnull(round((`information_schema`.`TABLES`.`INDEX_LENGTH` / 
`information_schema`.`TABLES`.`DATA_LENGTH`),4),0) AS `INDEX_DATA_RATIO` from 
`information_schema`.`TABLES` where 
(`information_schema`.`TABLES`.`TABLE_SCHEMA` not in 
('information_schema','performance_schema','mysql','common_schema'))

Original issue reported on code.google.com by gabr...@e-radical.ro on 19 Jul 2012 at 11:23

[GoogleCodeExporter]

Hi,

Thanks for the suggestion.

So basically this view is 1-1 with INFORMATION_SCHEMA.TABLES, but shows up 
prettier results?
This is fine, I have no objections and am willing to add this. I'm wondering if 
we could squeeze this a little bit further. How about InnoDB tables? The 
DATA_LENGTH includes the clustering index (which does not appear in 
INDEX_LENGTH). Any thing else we may add?

Original comment by shlomi.n...@gmail.com on 25 Jul 2012 at 1:28

[GoogleCodeExporter]

Just today, i was playing with this view, and i found i bit confusing, 
specially because i cannot understand the units! Maybe, you can "rewrite" the 
view in order to calculate the size in Mb, as it does this SQL:

SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS total_mb,
 ROUND(SUM(data_length)/1024/1024) AS data_mb,
 ROUND(SUM(index_length)/1024/1024) AS index_mb
 FROM INFORMATION_SCHEMA.tables
 GROUP BY table_schema 

Original comment by dtri...@gmail.com on 22 Nov 2012 at 8:16

[GoogleCodeExporter]

Hi,

The suggested view still does not exist. However:
1. It *does* provide data in MB
2. It provides (as the name suggest) data size per table; you are offering data 
size per schema. You must therefore be referring to 
http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/data_size_p
er_schema.html
Which is not the topic of this Issue.

At any case, I will not change an existing view's units due to compatibility; I 
might add "human" metrics as additional columns.

Original comment by shlomi.n...@gmail.com on 22 Nov 2012 at 8:24

[GoogleCodeExporter]

I didn't mean to change the existing view. Maybe, adding this extra columns 
will be fine!
Another option is to create another View. i don't think it cost a lot!

Original comment by dtri...@gmail.com on 22 Nov 2012 at 10:21