I made some large tables (around 3-4 billion records each) and then tried to create indexes on one column in each of them. I found that MySQL stopped at a seemingly arbitrary maximum index file size (.MYI file) for both of them. When I tried to open either table, I received an error message “ERROR 1016 (HY000): Can’t open file: ‘mytable.MYI’ (errno: 144)” indicating that the index file was crashed.

CHECK TABLE mytable QUICK reported ‘warning : Keyfile is almost full, 17179867136 of 17179867136 used’ Then myisamchk -dv mytable reported similar information under ‘Max keyfile length,” and reported “Keyfile pointer (bytes): 3′

After a little digging around, I realized that a 3-byte keyfile pointer only allows the index file to hold 2^24 blocks (about 16 million blocks of 1024 bytes each). My index file was holding about 110 keys per block (e.g., each record needed 4 bytes for the key value, plus 4 bytes for the datafile pointer, and maybe one byte for null/housekeeping, and then each block kept a little more empty space). So the 3-byte keyfile pointer only allowed MySQL to index about 1.8 billion records. Then it gave up, leaving a corrupted index file.

I have found no documentation for how MySQL decides how big a keyfile pointer to use in the .MYI file. I had no trouble adding 3-4 billion records to the MYD file, so it didn’t occur to me that there would be a problem with the MAX_ROWS clause of the CREATE TABLE command (which I hadn’t specified). But that turned out to be the problem.

I recovered my data table without the index file, then issued ALTER TABLE MAX_ROWS=4000000000, ADD INDEX (mykey); and now it looks like everything will be OK.

Here’s how I got my table back, without the index file (similar to what’s described above):

1. make a backup of the data file (mytable.myd), just in case
2. create a new empty table with the same structure as the existing table, but no index keys.
3. copy the .FRM and .MYI files from the new (empty) table over the existing mytable.frm and mytable.myi files.
4. issue REPAIR TABLE mytable USE_FRM; It is very important to include the USE_FRM clause. That way, MySQL knows to make a new (empty) .MYI file that matches the .MYD file, rather than truncating the .MYD file to match the .MYI file. You will eventually get a message along the lines of “Number of rows changed from 0 to xxxx”.
5. Now you have a working table, with no indexes. Then you can set MAX_ROWS to something appropriate, and try again to make the indexes.