Re: multiple-column index and order by

[Rick James]

I'll bet it did use the index. But it failed to give you any useful clues.

Note that it did say "Using index". This means that all the work was done in the INDEX, and the "data" was not touched.

Plan A: This may say more info (but I doubt it):
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;

Plan B: Upgrade. Later versions may say more.

Plan C: Here is how you can discover whether it actually read only 100 rows:
SHOW SESSION STATUS LIKE 'Handler_read%';
SELECT ...
SHOW SESSION STATUS LIKE 'Handler_read%';
Then subtract the values. I think one (or more) diff will be "100" and none will be 5 million.