On 05 Mar 2016 at 16:12, Richard Hipp <drh at sqlite.org> wrote: > On 3/5/16, Paul Sanderson <sandersonforensics at gmail.com> wrote: >> Clemens, >> >> Tim has the same issue as me, while >> >> SELECT EXISTS (select status from mytable where status=1); >> >> works the portion in brackets is still executed in full and this is >> what we are trying to avoid. > > The query planner in SQLite, while not brilliant, is smart enough to > know that it can stop and return true as soon as it sees the first row > from the query inside of EXISTS(). > > So if there is an index on mytable.status, the above will be very > query. Or if there are instances of status=1 early in mytable, the > above will be quick. But if mytable.status is never 1 and if there is > no index on mytable.status, then SQLite will scan the entire table to > verify that status is never 1 before returning 0. I don't see anyway > around the latter case, however.
Thanks - that allows me to use that method with the confidence that it's optimum. That index should always exist, in my case. -- Cheers -- Tim
