Re: Big problems on optimizing query

[Rick James]

What version are you running? Only recently did MySQL allow more than 4GB for key_buffer.
key_buffer_size | 34359738368
is unreasonably for a 48GB machine. 12GB would be better. The key_buffer only caches MyISAM indexes. The rest of RAM is used for caching the data, which is usually much bigger.

The table you showed add up to only 10GB of data and 20GB of index.

I see no compound keys, and what might be an excess of single-column keys.
INDEX(lastname, firstname) is usually a lot more useful than
INDEX(lastname), INDEX(firstname)

MyISAM does not handle FOREIGN KEYs, so I assume your "fk_" prefix is just for you.

Normalizing out the Date is not a good idea. Period.

SELECT 
    ->   * 
    -> FROM 
    ->   strip_Date d,
    ->   strip_MetaData md
    -> WHERE 
    ->   d.id = md.date_id
    ->   and md.costCenter_id = 107
    ->   and d.date = '2010-07-08'
    ->   and md.approvalNumber_id = 15101

md needs
INDEX(approvialNumber_id, costCenter_id)
and/or
INDEX(date_id, approvialNumber_id, costCenter_id)
and strip_Date should have
INDEX(date, id)
BUT, as I say, this should really be

SELECT 
    ->   * 
    -> FROM 
    ->   strip_MetaData md
    -> WHERE 
    ->   and md.costCenter_id = 107
    ->   and md.date = '2010-07-08'
    ->   and md.approvalNumber_id = 15101

at which point, this may be the best index:
INDEX(date, costCenter_id, approvalNumber_id)
(actually, any arrangement of those three columns would work about as well)
Separate, single-column, indexes will _not_ work as well.

Unless you have some argument for normalizing out approvalNumber, I would argue against it:
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`approvalNumber` varchar(9) DEFAULT NULL,

Once you denormalize those two, and add a suitable compound index, your other query will speed up nicely.

This one may be more reasonable to be normalized, since the costCenter is (I guess) rather large:
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`costCenter` varchar(64) DEFAULT NULL,
But, considering that there are very few const centers, I would recommend SMALLINT UNSIGNED instead of BIGINT SIGNED; this will shrink the id from 8 bytes to 2. (Max will be 65535.)

Sorry if I sound too harsh. Normalization is great, but you have some examples where it should not be taken as gospel.