MySQL Forums
Forum List  »  MyISAM

Re: Select * from MyISAM table returns PK order / filesort order
Posted by: Rick James
Date: March 23, 2009 10:42PM

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.

Options: ReplyQuote

Written By
Re: Select * from MyISAM table returns PK order / filesort order
March 23, 2009 10:42PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.