Optimize a table in MySQL from the command line interfaceOptimize a table in MySQL from the command line interface

Posted September 4th, 2008 in MySql

If you have MySQL tables that grow large and then have a lot of deletes they will become fragmented and larger than they need to be. This post looks at how to look at check to see if a MySQL table is no longer optimal and how to optimize it.

Tomorrow's post will look at how to do the same thing with phpMyAdmin instead of issuing the commands yourself (i.e. you can just point and click) and then on Sunday a PHP script to automatically optimize all tables that need it within set parameters.

Log into the MySQL command line interface, select your database and then issue the 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 resultset on a new line, whereas ; will show the columns across the screen. This is OK for a resultset 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. If this is high, as in the above example where 564614700 bytes are free (538MB), then the table has a lot of space not being used and should 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 (it may take a few seconds dpending on the size of the table, free space etc) 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)

In the above example we can see the "Data_free" value is now zero so the table is nicely optimised.

Tomorrow I'll look at how to do the same thing with phpMyAdmin so all you need to do is point and click instead of having to remember the commands and type them in.

Related posts:

Recent posts: