On Mon, Aug 22, 2005 at 08:44:07AM +0800, Damian Slee wrote: > >>In order to implement the ORDER BY clause, SQLite reads the entire result > >>set into memory and sorts it there. When your result set gets very large > >>(13000 rows) and each row uses in excess of 1KB or memory,this is > >>apparently causing your machine to thrash. > > > If an index exists on the same column that Order By is used, does this still > result in reading the entire result set?
Yes. It seems you have to trust the database, and avoid the 'order by' when you want the result to be sorted according the primary key. Actually I think it is a small bottleneck which should be solved, and it will really make sqlite the best database solution. I had switch to mysql (i am checking pgsql too), and it seems to be working fine. The problem seems to be that the whole row is loaded, which is not acceptable since there are even scenarios where you store entire images into database. The row size should not ever be an issue when dealing with the table. Thanks.
