MySQL Forums
Forum List  »  MyISAM

Select * from MyISAM table returns PK order / filesort order
Posted by: Herman Darmawan
Date: March 22, 2009 08:28PM

Hi,

I have a transaction table that is running on MYSQL/MYISAM that is 15 million records long.

It is indexed by PK on ID which is an identity column.

I need to pull all rows nightly to sql server 2000 for ETL purposes.
I need the rows to be in PK order, I thought, by using a simple
select * from journal will get the rows sorted by PK order.

(The ID starts at 870337)

These are my findings...

Select * from journal
Result: filesort order

Select *
From journal
Where ID between 879337 and 879337+4307517
Result: PK index order

Select *
From journal
Where ID between 879337 and 879337+4307518
Result: filesort order

Select *
From journal
Where ID < 5186855
Result: filesort order

Select *
From journal
Where ID between 0 and 5186854
Result: PK index order

Select *
From journal
Where ID between 1000000 and 5307471
Result: PK index order


Select *
From journal
Where ID between 1000000 and 5307472
Result: filesort order

The common situation
Is that if row count is less than 4282611, it is in order.
Otherwise it is not in order.

Has anyone encountered this?
What's so magic of about the number 4282610?
Is it related to the structure of the number of rows in an ISAM index?

Herman

Options: ReplyQuote


Subject
Views
Written By
Posted
Select * from MyISAM table returns PK order / filesort order
4886
March 22, 2009 08:28PM


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.