Re: Select * from MyISAM table returns PK order / filesort order

[Rick James]

Congratulations! You discovered the magical 30%. There has long been a rumor floating around that goes something like this...

The query optimizer decides between using an index and ignoring the index, doing an table scan instead. The dividing line is at 30%.

More details... You are using MyISAM, which knows exactly how many rows are in the table. And by probing the PRIMARY KEY (stored in a BTree in the .MYI file), it can get a pretty good estimate of how many rows are between, say 1000000 and 5307471. (I suspect it does not calculate _exactly_ how many rows, because BTree nodes can vary in how many items they represent.) Then it applies the 30% rule.

Below 30%, it says "I think it would be faster to walk sequentially thru the index, reaching (random access) over into the data".

Above 30%, it says "That's a lot of random lookups; let's not even touch the index, instead just scan the table, skipping the rows he doesn't want."

Since you did not give an ORDER BY (which might have further biased its choice), it simply delivered the rows to you in whatever order it fetched them -- PK order if using the PK, file order if doing a table scan.