Contents
How do I shrink a table in MySQL?
Run the following command to optimize the table and shrink the file size: OPTIMIZE TABLE ; Note: Replace with the name of the table you want to optimize.
How do you optimize a table in a database?
Optimizing your database
- Select your database from the list.
- Look at the Overhead column to the far right – if you see any numerical values, those tables can be optimized.
- At the bottom of your table list, click the With selected dropdown menu.
- From the dropdown menu, select Optimize table.
How do I optimize InnoDB tables?
OPTIMIZE TABLE for an InnoDB table is mapped to an ALTER TABLE operation to rebuild the table and update index statistics and free unused space in the clustered index. This operation does not use fast index creation.
Why is mysql.ibd file too big?
If mysql is unable to optimize it, it will analyze and recreate it automatically for you. Be warned that the table is locked during the duration of this command which can be a lengthy process. It appears that *.ibd files (like the file ibdata1) by default never shrink, they can only grow.
How to tell the size of an ibdata file?
If you are convinced the data dictionary is the cause, there is only one sure way you can verify this. The number that comes out will be the size will be the size of ibdata1 with just data dictionary entries for the current list of tables, but with no data in them.
Is the.ibd file in Ubuntu too big?
Be warned that the table is locked during the duration of this command which can be a lengthy process. It appears that *.ibd files (like the file ibdata1) by default never shrink, they can only grow. They are created if you have configuration set (which is the default on ubuntu).
Why is the InnoDB file per table too big?
Somewhat related, even with innodb_file_per_table turned on, the idbdata1 file can grow “a bit” (and never shrinks) because it still includes rollbacks for “your largest transaction to date”. Go figure.