Re: [sqlite] Is this query optimized?

[Kurt Welgehausen]

count() has always done a full table scan. As far as I know,
nothing has been done to optimize it, as your observations
seem to confirm.

Regards

Re: [sqlite] Is this query optimized?

[R S]

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  and )
> 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  and ) 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!
> >
> >
> >
>

Re: [sqlite] Is this query optimized?

[Martin Engelschalk]

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  and ) 
  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  and ) 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!

 

[sqlite] Is this query optimized?

[R S]

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  and ) 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!