I've had an error message telling about index corruption on the .myi file

Reparing the table fixed the issue

This table is accessed with select only, so what is likely to corrupt indexes ?

CREATE TABLE `my_table` (
 `col1` mediumint(1) unsigned NOT NULL,
 `col2` mediumint(1) unsigned NOT NULL,
 `fk_brand_trans` smallint(1) unsigned NOT NULL,
 `ref_trans` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `col3` smallint(1) unsigned NOT NULL,
 `attribute` smallint(1) unsigned NOT NULL,
 `ref` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `pos` tinyint(1) NOT NULL,
 KEY `veh` (`col2`,`col1`),
 KEY `trans` (`ref_trans`,`fk_brand_trans`) USING BTREE,
 KEY `brand` (`fk_brand_trans`)

50M rows, 1.8GB data, 1.4GB index

  • 1
    A gust of wind ... Commented Aug 28, 2018 at 15:59

1 Answer 1


Since the MyISAM storage engine only caches indexes, reads from .MYI files are the norm. If the .MYI file of a given MyISAM table never changes, you have to look into external factors.

Just keep in mind that if the index blocks for a MyISAM table are not in the Key Cache, it must be read from disk, then loaded into the Key Cache.


If the disk blocks that hold the .MYI file become corrupt, there may be an error reading index blocks. Doing the REPAIR TABLE must have written the rebuilt .MYI elsewhere on disk.


If the memory chips become defective and parts of the MyISAM Key Cache is in use there, the .MYI file could possibly be considered corrupt when reading index blocks. Doing the REPAIR TABLE creates a new .MYI and is freshly read into the MyISAM Key Cache.


To reduce or eliminate the amount of reads from the .MYI file of a MyISAM table, you should look into setting up a dedicated Key Cache just for this table.

Since your table has a 1.4GB .MYI file, make a 1.5GB dedicate key cache for it:

USE mydb
SET GLOBAL my_table_keycache.key_buffer_size = 1536 * 1024 * 1024 * 1024;
CACHE INDEX mydb.my_table INTO my_table_keycache;
LOAD INDEX INTO CACHE my_table_keycache;

I have suggested this before

Doing this will also let you know quickly if the external factors apply in the future.

  • The disk is a Samsung NVMe SSD and the memory is ECC. Yet if this happens again and if the cause is Factor #2, what is the best solution (other than repair table which took a while, like running a command or restarting the server) ?
    – guigoz
    Commented Aug 28, 2018 at 20:40
  • Math wrong... Don't set the key buffer to 1.5TB.
    – Rick James
    Commented Sep 30, 2018 at 3:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.