Re: [sqlite] Quickest way to get an answer

[Pavel Ivanov]

>  select count(status) from mytable where status=0;
>  select count(status) from mytable where status=0 limit 1;

These two are identical because query always return one row thus
making "limit 1" a no-op.

>  select status from mytable where status=0 limit 1;

This one will have the best performance because SQLite won't have to
find all rows matching the WHERE condition, it will be able to stop on
the first row found.


Pavel


On Fri, Nov 11, 2011 at 2:27 PM, Tim Streater  wrote:
> My db has a column called "status". This can take one of 7 or so integer 
> values from 0 to 7 or so. There are times when I need a quick answer to this 
> question: are there any rows in the db for which status has value 0. I don't 
> need to know how many, just whether there are any or not. What's the least 
> expensive form of making this query?
>
> So far I've tried:
>
>  select count(status) from mytable where status=0;
>  select count(status) from mytable where status=0 limit 1;
>  select status from mytable where status=0 limit 1;
>
> When doing this a number of times I see some seconds of CPU being taken; I 
> haven't yet pinned it down to being an SQLite problem - I'm about to do some 
> timings to see where the time is going. I've added an index:
>
>  create index stat on mytable (status asc);
>
> and using the third form above together with an index seems to improve 
> matters a bit.
>
> If all the forms above are roughly equivalent that would be helpful to know. 
> There are 3000 or so rows in the table.
>
> --
> Cheers  --  Tim
>
> ___
> 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

Re: [sqlite] Quickest way to get an answer

[Simon Slavin]

On 11 Nov 2011, at 7:27pm, Tim Streater wrote:

>  select count(status) from mytable where status=0;
>  select count(status) from mytable where status=0 limit 1;
>  select status from mytable where status=0 limit 1;
> 
> When doing this a number of times I see some seconds of CPU being taken; I 
> haven't yet pinned it down to being an SQLite problem - I'm about to do some 
> timings to see where the time is going. I've added an index:
> 
>  create index stat on mytable (status asc);
> 
> and using the third form above together with an index seems to improve 
> matters a bit.

The index is definitely going to improve this tremendously.  I like your third 
form from the above: try to get just one line, and see whether you get one line 
or none.  I think it'll work best.  But you might want to compare its speed with

select count(*) from mytable where status=0

The 'count(*)' expression gets treated specially.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Quickest way to get an answer

[Tim Streater]

My db has a column called "status". This can take one of 7 or so integer values 
from 0 to 7 or so. There are times when I need a quick answer to this question: 
are there any rows in the db for which status has value 0. I don't need to know 
how many, just whether there are any or not. What's the least expensive form of 
making this query?

So far I've tried:

  select count(status) from mytable where status=0;
  select count(status) from mytable where status=0 limit 1;
  select status from mytable where status=0 limit 1;

When doing this a number of times I see some seconds of CPU being taken; I 
haven't yet pinned it down to being an SQLite problem - I'm about to do some 
timings to see where the time is going. I've added an index:

  create index stat on mytable (status asc);

and using the third form above together with an index seems to improve matters 
a bit.

If all the forms above are roughly equivalent that would be helpful to know. 
There are 3000 or so rows in the table.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users