MySQL: Check database size and optimize to shrink size

mysql-check-dbsize-and-shrink.sql

-- https://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database
mysql> 
SELECT table_schema 
  "DB Name", 
  Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema;

-- RESULT:
-- +-----------------------+---------------+
-- | DB Name               | DB Size in MB |
-- +-----------------------+---------------+
-- | japanpost             |          36.6 |
-- | mysql                 |           7.6 |
-- | performance_schema    |           0.0 |
-- | sys                   |           0.0 |
-- | test                  |           0.0 |
-- +-----------------------+---------------+
-- 5 rows in set (0.02 sec)

-- ***********************************************************
--  Optimize a table
-- ***********************************************************
-- Optimize zipcode table of japanpost db:
mysql> \r japanpost
mysql> optimize table zipcode;

-- +-------------------+----------+----------+-------------------------------------------------------------------+
-- | Table             | Op       | Msg_type | Msg_text                                                          |
-- +-------------------+----------+----------+-------------------------------------------------------------------+
-- | japanpost.zipcode | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
-- | japanpost.zipcode | optimize | status   | OK                                                                |
-- +-------------------+----------+----------+-------------------------------------------------------------------+

-- Check size again:
-- +-----------------------+---------------+
-- | DB Name               | DB Size in MB |
-- +-----------------------+---------------+
-- | japanpost             |          23.1 | <= shrunk!


-- ***********************************************************
--  Optimize all tables of a database
-- ***********************************************************
-- optimize all tables of japanpost database:
$ mysqlcheck -o -h localhost -uroot -p -o japanpost

-- ***********************************************************
--  Have to flush tables after table optimizing
-- ***********************************************************
mysql> flush tables;