> Suppose, if I don't have index, is this the desired behavior of Sqlite > to take this much time to fetch just 10 records or am I missing > something here.
You're missing that SQLite have to fetch all records satisfying your condition into memory storage, sort all these records in memory and then pick 10 first out of them. So in this particular case it could be expected. And I think index on column b should speed it up. Pavel On Thu, Jul 16, 2009 at 1:22 PM, MADHAVAN VINOD<vinod.madha...@alcatel-lucent.com> wrote: > > Hello Michal, > > Thanks for the reply. Please see my comments inline. > >>>if you always have condition a=1 (or something similar which uses = > for >>>comparison) you should have index which starts with this field. > > The possible values for this field are 1/2. And mostly all the records > have the value 1. Hence I do not see any improvement in the query even > when I created index on this column (a). > > > Moreover, I tried simplifying the WHERE clause to have just "a=1 AND b < > c AND d=0" and ORDER BY clause to have just "b ASC". > > The execution of this query itself took me around 3 seconds. > > Suppose, if I don't have index, is this the desired behavior of Sqlite > to take this much time to fetch just 10 records or am I missing > something here. > > > Best Regards, > Vinod N.M. > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michal Seliga > Sent: Thursday, July 16, 2009 8:37 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Performance issue on records retrieval :: 100,000 > records > > > > MADHAVAN VINOD wrote: >> >> 5) No INDEX created. >> >> The retrieval logic is such that to retrieve the oldest 10 records > along >> with some additional constraints (say a, b and c are columns and the >> constraints are like a=1 AND b < c). >> >> >> >> So my WHERE clause is like "CurrTime <= ExpireTime AND CurrTime >= >> NextProcessingTime AND a=1 AND b < c" >> >> And my ORDER BY clause is " CurrTime - NextProcessingTime DESC, b ASC" >> >> >> >> > > > you need index for this, otherwise lookup goes through whole table > question is what index would help you the most. > > now i am not sure if i understood you correctly, are ExpireTime and > NextProcessingTime database fields? if yes, then in addition you should > have at > the end of index columns one of ExpireTime or NextProcessingTime, you > should > choose one which can help you more (one which will help database engine > to limit > row count the most) > > so for situation you wrote i would recommend to have one of indices: > create index ... on ...(a,ExpireTime) > or > > create index ... on ...(a,NextProcessingTime ) > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
