Thanks Richard so suppose I have two tables table1 and table2 each with 1000 rows and say 100 columns some containing large blobs.
My user choose a query "select * from table1, table2" I can modify the query and do a "select count(*) from table1, table2" to determine that 1M rows will be returned and ask him if this is what he really wants to do. But what if he has "select * from table1, table2 limit 1000" In this instance there is no need for me to warn him as he has already applied a limit to the amount of data returned. Any suggestions as to how I can detect this , other than parsing the query for "limit x" Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 5 March 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. > > -- > D. Richard Hipp > drh at sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
