MySQL Thread States Repair By Sorting Vs. Repair With Key Cache

When perform any of the REPAIR, ALTER or LOAD or ENABLE KEYS command on a MyISAM table, it also causes the rebuilding of the indexes. Indexes are rebuilt (repaired) in two modes:

•  Repair by sorting -- The repair code is using a sort to create indexes.
•  Repair using keycache -- The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.

To start repairing, first it tests if the table can be repaired by sorting provided it meets the following requirements:

•   Table at least has one key 
•   Total size needed for individual key is less than myisam_max_sort_file_size

If it meets the above requirements, then it uses either regular sorting if myisam_repair_threads = 1 (default) by building each key at a time or in parallel if myisam_repair_threads > 1 by using ‘n’ threads in parallel (n = total keys in the table).

 
If it fails to satisfy the above conditions, then it falls to expensive keycache repair mode.

Therefore, to avoid keycache repair, set the myisam_max_sort_file_size to the max index size.

 Related Note:

•  MyISAM_MAX_SORT_FILE_SIZE Variable

References:

• MySQL Manual: General Thread States
• Book:High Performance MySQL
• Other reference on the web