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. if you always have condition a=1 (or something similar which uses = for comparison) you should have index which starts with this field. 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