Re: .ini settings for ADD KEY to 30Gb table on 96Gb machine?

[Rick James]

Since you are using 5.5, suggest looking into PARTITION instead of MERGE. However, if the only purpose for splitting the table is to deal with reindexing, then maybe MERGE would actually be better. (I don't know.)

Your first SELECT begs for
INDEX(PointID)
which is taken care of by
PRIMARY KEY (`PointId`, `ModisTileLoadId`)
(if you keep that)

Your 8min SELECT also needs either of those INDEXes).

`Blue` smallint(6) DEFAULT NULL,
I assume your resolution is more than 8 bits and no more than 15.

"She suggested setting the Primary Key to ModisTileLoadId, PointId and then the primary key would be exactly the load order if I sorted the files correctly." --
She is partially right. The _data_ for MyISAM is loaded into the .MYD file in the order presented -- the PK does not matter. But each index also needs to be built; this may involve random actions. An index approximating the data-order will be efficiently built. Other indexes may be very slow if they use the key buffer. (InnoDB works quite differently, but not faster for your table.)

ENGINE = MEMORY -- 80GB may work.

Consider doing a disk sort (get cgwin?) so that the LOAD DATA file is in the PRIMARY KEY order.

Do you ever need to SELECT based on a range of ModisTileLoadId? (other than the case given, where the WHERE clause also restricts to a range over PoinID) If so, PARTITION may be useful.

Will you be querying more than one 'year' at a time? If so, this could add another dimension of complexity. And may again beg for PARTITION.