Other day I noticed a case where loading the same set of data to InnoDB took only 10 minutes where as loading it to MyISAM took ~2 hours.

Digging it further found that it is all because of well known Repair with keycache issue. But for some reason, it took me a while to get to the root cause of the issue as it was working fine until few days. When MyISAM needs to repair the table (REPAIR, ALTER or LOAD or ENABLE KEYS); it uses two modes for repair:

• repair by sorting
• repair using keycache (falls to this mode by default if repair by sort fails for any reason)

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 …