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
