On 05 Mar 2016 at 13:05, Clemens Ladisch <clemens at ladisch.de> wrote:
> Paul Sanderson wrote: >> I am trying to determine before a query is executed how many rows will >> be returned. the following query works as expected >> >> select count(*) from table >> >> but >> >> select count(*) from table limit 100 >> >> still returns the number of rows in the table not the number of rows >> that would be returned by the query. > > "The query" is the one that has the "limit" clause. What else should > the database execute? > > As documented <http://www.sqlite.org/lang_select.html>, the result rows > are generated before the LIMIT clause is applied. > > To determine how many rows would be returned by an arbitrary query, use: > > SELECT COUNT(*) FROM (SELECT ...); > > But why do you want to determine the number of rows in the first place? In my case I want to know whether at least one row exists that has a certain column which has a given value. At the minute I do this: select count(*) from mytable where status=1 limit 1; and mytable has this index: CREATE INDEX stat on mytable (status asc); Would this: select count(*) from (select status from mytable where status=1 limit 1); or some other query be faster. Really, I'd like SQLite to stop after finding one row. -- Cheers -- Tim
