Contents
How do I make my MySQL database smaller?
6 Easy Tips to reduce the size of MySQL Database
- Backup, first but not least.
- List MySQL Table and Index Size.
- Delete Unwanted Data.
- Find and Remove Unused Indexes.
- Shrink and Optimize MySQL.
- Optimize Datatypes for Columns.
- Enable Columns Compression (Only InnoDB)
- Compress Table (Only MyISAM)
Does delete Free table space?
Space is effectively reused when you delete. Your database will not show any new free space in dba_free_space — it will have more blocks on freelists and more empty holes in index structures.
How can I reduce the size of MySQL database?
OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depending on the storage engine used by that table.
Do you need to shrink the database after dropping a table?
Dropping a table will free up the space within the database, but will not release the space back to Windows. That requires shrinking the database file. However we don’t want the database file full. We want lots of free space so that as we load more data we don’t have to grow the data file frequently.
What to do if you drop a table in MySQL?
If you drop some tables and delete some data, then there are two ways to reclaim that unused disk: Change the storage engine and revert it back to your previous configuration. This will definitively recreate the table and indexes from the start and unused disk space will be reclaimed.
How to reduce table size in MySQL InnoDB?
Be sure to also retain a copy of your schema in your dump. Currently, you cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure: Use mysqldump to dump all your InnoDB tables. Stop the server. Remove all the existing tablespace files, including the ibdata and ib_log files.