> 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 <t...@clothears.org.uk> 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
