Our MySQL tables often grow large and we do a lot of deletes. This will make our tables fragmented and larger than they need to be. Today we will check a MySQL table which is no longer optimal and optimize it.
After Logging into the MySQL and selecting the database, issue this command below, where ‘mytablename’ is the name of the table you want to query:
show table status like 'mytablename'G
You can omit the “like ‘mytablename’” part and then it will show this information for all tables. However if you have a lot of tables and there’s only one or two you want to examine then it’s better to specify the particular table.
You can end you query with either ; or G. I prefer G for this particular query because it shows each column from the result set on a new line, whereas ; will show the columns across the screen. This is OK for a result set with only a few columns with only a small amount of information in each one, but it’s not so good for this query.
The result from the above will look something like so:
*************************** 1. row ***************************
Name: mytablename
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2444
Avg_row_length: 7536
Data_length: 564614700
Max_data_length: 281474976710655
Index_length: 7218176
Data_free: 546194608
Auto_increment: 1187455
Create_time: 2008-03-19 10:33:13
Update_time: 2008-09-02 22:18:15
Check_time: 2008-08-27 23:07:48
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: pack_keys=0
Comment:
The values that are important for working out if the table is non optimal is the “Data_free” value. The value is given in bytes and it can be optimized.
To optimize the table, issue the following command, where “mytablename” is the name of the MySQL table to optimise:
optimize table mytablename;
After doing this and running “show table status” again, the result should look much better:
*************************** 1. row ***************************
Name: tblmailqueue
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 6145
Avg_row_length: 7505
Data_length: 46119636
Max_data_length: 281474976710655
Index_length: 296960
Data_free: 0
Auto_increment: 1191156
Create_time: 2008-03-19 10:33:13
Update_time: 2008-09-02 22:24:58
Check_time: 2008-09-02 22:21:32
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: pack_keys=0
Comment:
1 row in set (0.00 sec)
The above example has zero as “Data_free” value, so the table is nicely optimized.