MySQL Database Optimization

This is specially tested with Snort IDS Database

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.

You can do something like this:
SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";") FROM tables WHERE DATA_FREE > 0 INTO OUTFILE '/tmp/optimize.sql'; SOURCE '/tmp/optimize.sql';