(Too much clutter for Comments; hopefully some of the answers to these questions will help lead to a real Answer.)

(4s is only 10x 400ms.)

You say "unique MemberNumbers"; perhaps you did not mean that?

How many rows have MemberNumber = 1234? How many rows are returned from the query?

Is the nightly data just incremental inserts? Or is it a complete reload?

What value of key_buffer_size were you using?

Please provide SHOW TABLE STATUS LIKE 'memberclaim' for each engine.

The table, plus indexes, take about 50% more space than necessary if you could shrink all the BIGINTs to INTs.

Please run (and provide output from) this for each engine:

EXPLAIN SELECT ...;

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

That will give some more clues of how many reads it is really doing.

(First some comments; then a probable Answer.)

(4s is only 10x 400ms.)

How many rows have MemberNumber = 1234? How many rows are returned from the query?

What value of key_buffer_size were you using?

Please provide SHOW TABLE STATUS LIKE 'memberclaim' for each engine.

The table, plus indexes, take about 50% more space than necessary if you could shrink all the BIGINTs to INTs.

Please run (and provide output from) this for each engine:

EXPLAIN SELECT ...;

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

That will give some more clues of how many reads it is really doing.

Timings

If 254 records (in the data) are touched in 2.265 seconds, that says about 9ms/record, which is roughly the speed of spinning disks, when none are cached. Please run the query twice in a row -- and see what the two timings are; probably the second one would be much faster, thereby confirming that the first run was non-cached.

Further 9ms/row implies that the rows are scattered around the table. This is likely to happen in either MyISAM or InnoDB when the index being used does not 'track' the order of the data insertion. Is the query in question the most important one? If so, the performance can be fixed in the following way. (Caution; the following will hurt performance on any range scan of ClaimNumber, but I suspect you don't do such.)

Answer

Change to InnoDB, so that the PRIMARY KEY is 'clustered' with the data and change from

PRIMARY KEY (ClaimNumber),
KEY idx_MemberNumber (MemberNumber, ServiceFrom, ServiceTo),

to

PRIMARY KEY (MemberNumber, ClaimNumber),
INDEX(ClaimNumber),

I think having all rows for MemberNumber = 1234 "clustered" together will give you enough performance boost to make this worthwhile. I left off the From and To; one of them could be added in the middle of the PK, but I don't think it will help a lot.

You will have about 100 rows per block. If, say, there are 530 rows for 1234, then that is 6 blocks to read -- about 60ms, which is a lot better than the 254 random blocks read (2265ms), as surmised above. Shrinking all the BIGINTs to INT UNSIGNED would get you down to about 40ms.

If you have other SELECTs that look at multiple rows for a single "member", they will also run faster.

Added bonus: Since fewer blocks are read for this query, other queries will find the cache (innodb_buffer_pool) more useful.

(This technique will not work for MyISAM because the data is not kept in any particular order. Also, UPDATEs tend to further mangle the data.) (Another note about MyISAM: It has been removed from the next major release, 8.0.)