How To Optimize MySQL Tables

How To Optimize MySQL Tables

mysql> show table status like "user" \G

*************************** 1. row ***************************

           Name: user

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 6

 Avg_row_length: 115

    Data_length: 692

Max_data_length: 281474976710655

   Index_length: 2048

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2022-04-16 04:36:27

    Update_time: 2022-04-16 04:36:27

     Check_time: 2022-04-16 07:13:50

      Collation: utf8_bin

       Checksum: NULL

 Create_options:

        Comment: Users and global privileges

1 row in set (0.00 sec)

The output shows some general information about the table. The following two numbers are important:

Data_length represents the amount of space the database takes up in total.

Data_free shows the allocated unused bytes within the database table. This information helps identify which tables need optimization and how much space will be released afterward.

Show Unused Space for all tables.

select table_name, data_length, data_free from information_schema.tables where table_schema='user' order by data_free desc;

Display Data in Megabytes.

select table_name, round(data_length/1024/1024), round(data_free/1024/1024) from information_schema.tables where table_schema='user' order by data_free desc;

Optimize a Table Using MySQL

optimize table user;

Optimize Multiple Tables at Once

optimize table user, db, proc;

Optimize Tables Using the Terminal

Syntax: 

mysqlcheck -o <schema> <table> -u <username> -p <password>

mysqlcheck -o <schema> <table> -u <username> -p <password>