MySQL Performance Tunning

MyISAM

1.  Table Row_Format

http://dev.mysql.com/doc/refman/5.1/en/static-format.html

show table status

Row_format =  Fixed / Dynamic

Fixed is better for select , but the table can't contain variable-length columns (VARCHAR, VARBINARY, BLOB, TEXT)

ALTER TABLE DBNAME.TABLENAME ROW_FORMAT=Fixed

Static-format tables have these characteristics:

• CHAR and VARCHAR columns are space-padded to the specified column width, although the column type is not altered. BINARY and VARBINARY columns are padded with 0x00 bytes to the column width.
• Very quick.
• Easy to cache.
• Easy to reconstruct after a crash, because rows are located in fixed positions.
• Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk -r.
• Usually require more disk space than dynamic-format tables.

2. OPTIMIZE TABLE
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

For MyISAM tables, OPTIMIZE TABLE works as follows:

1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

By default, the server writes OPTIMIZE TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

•