Re: [sqlite] Quickest way to get an answer
> 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 Streaterwrote: > 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
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
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