Actually I have an index on both columnVal (a varchar) and insertTime (bigint).
On 10/25/05, Martin Engelschalk <[EMAIL PROTECTED]> wrote: > > Hi, > > if the where clause in your subquery is not supported by an index (and i > suspect this is the case), sqlite has to do a full table scan. > Therefore, the execution time is you observe is to be expected. > I think thar you can change your query to something like (i didn't test > it,) > > select count(*) as totalCount > from myTable > where (insertionTime BETWEEN <beginTime> and <endTime>) > and columnVal > '0' > group by columnVal > having totalCount > 10 > > If you also create an index on insertionTime, your query should be fast. > > Martin > > > R S schrieb: > > >Hi, > >I am trying to use this query and notice that the execution time > increasing > >linearly as the Table size increases. > > > >select totalCount from (select count(*) as totalCount from myTable where > >(insertionTime BETWEEN <beginTime> and <endTime>) and columnVal > '0' > group > >by columnVal) where totalCount > 10; > > > >Diff between beginTime and endTime is always constant. > >columnVal is a varchar. > >aColumn is an integer. > > > >Thanks! > > > > > > >
