MySQL Forums
Forum List  »  MyISAM

Re: .ini settings for ADD KEY to 30Gb table on 96Gb machine?
Posted by: Rick James
Date: November 03, 2010 08:57AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.