How to maintain MySQL MyISAM Table

You should maintain your MySQL database table to prevent from corruption. MyISAM Table maintenance can be done by using these four commands:  

1.CHECK TABLE: use to check MyISAM tables 
2.REPAIR TABLE: use to repair MyISAM tables, 
3.OPTIMIZE TABLE: use to optimize MyISAM tables
4.ANALYZE TABLE: use to analyze MyISAM tables

First Check your table: CHECK TABLE: This command checks your tables for errors.

CHECK TABLE tbl_name [, tbl_name] ... [option] …

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

Type	Meaning
QUICK	This option don't check for incorrect links when scan the row.
FAST	This option only checks tables that are not properly closed.
CHANGED	This option only checks tables that are changed since the last check.
MEDIUM	This option scans the rows to verify that deleted links are valid.
EXTENDED	This option check that the table is 100% consistent, but takes a long time.

If your table is already gets corrupted then you should use REPAIR TABLE command to successfully repair your database table. If your tables get corrupt then maybe you get an error message just like this 'incorrect key file for table: '...'. Try to repair it' or when you run Queries then it returns incomplete results or don't find rows in the table. 

There is various cause of MyISAM Table Corruption: 

• Sudden computer shutdown 
• Hardware failures.
• mysqld process get stop in the middle of a write.
• Software bug in the MySQL or MyISAM code. 
• & many more..

REPAIR TABLE: This command repairs a possibly corrupted table.

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name]...
    [QUICK] [EXTENDED] [USE_FRM]

Type	Meaning
QUICK	This option repairs only the index file, and not the data file.
EXTENDED	This option creates the index row by row .
USE_FRM	This option used when .MYI index file is missing or if its header is corrupted.

OPTIMIZE TABLE: This command used to reduce storage space and improve I/O efficiency when accessing the table.

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name]...

When you delete the large part of a MyISAM table then these Deleted rows are stored in a linked list. In this case, You can use OPTIMIZE TABLE to reclaim the unused space. So we can say you should use this command when:

1.If the table has large part deleted rows
2.If the index pages are not sorted
3.If the table's statistics are not up to date

ANALYZE TABLE: This command used to analyzes the table and stores the key distribution for a table. Key distribution used to decide the order in which tables should be joined & when you perform a join on the table.

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name]...

So this is all about MyISAM Table maintenance. You can also use mysqlcheck & myisamchk to check & repair your MyISAM Table.