Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace
- FROM information_schema.tables
- WHERE engine='InnoDB';
- SELECT
- IFNULL(tbl,'Total') table_name,
- FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size
- FROM
- (
- SELECT table_name tbl,SUM(data_length+index_length)table_bytes
- FROM information_schema.tables WHERE table_schema='mydb
- GROUP BY table_name WITH ROLLUP
- ) A ORDER BY ISNULL(tbl) DESC,table_bytes;
- SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
- FROM information_schema.tables
- WHERE table_schema='mydb;
- AND table_name='mytable';
- use mydb
- CREATE TABLE mytable_new LIKE mytable;
- INSERT INTO mytable_new SELECT * FROM mytable;
- ANALYZE TABLE mytable_new;
- SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
- FROM information_schema.tables
- WHERE table_schema='mydb;
- AND table_name='mytable_new';
- #
- # Stop Here. If you are happy with the size, do the last two commands
- # If you prefer, drop table mytable_new and do do the online schema change
- #
- RENAME TABLE mytable TO mytable_old,mytable_newTO mytable;
- DROP TABLE mytable_old;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement