Quote
InnoDB seems to have decided that using the data file will be just as fast.
I'll read that section, but it makes absolutely no sense at all to me how scanning most (or even much) a 9 million+ row table could possibly be as fast as using an index that exactly matches both your search criteria and the columns you're retrieving only 21 rows for...no matter how the buffer pool is configured (all settings are at their installation defaults currently).
Quote
What's the performance without the Limit clause?
Eliminating the LIMIT clause has no effect on the EXPLAIN results, except for the number for "rows" (9316207 instead of 21). Naturally the performance is far worse.
Quote
Did you try Force Index, and if so with what result?
Using FORCE INDEX (PRIMARY) has absolutely no effect on either the EXPLAIN results or the actual query performance.
I've tried many, many workarounds (changing character set to 'latin1', etc) based on similar reports I've found in other forums going back as far as 2010. None have had any impact at all. I can scarcely imagine a more simple case in which use of a compound key is obviously the correct...and really only rational...strategy for implementing a query, and where one really should not need to resort to tricking the optimizer into doing so.