Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/11 18:52, Simon Slavin wrote: > I'll just use LIMIT 51 on my query and if I get 51 results back I'll > know there are too many for the query to be useful. Incidentally you don't need to add a limit on the query. In the code that calls

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Simon Slavin
On 8 Oct 2011, at 2:27am, Roger Binns wrote: > Although various people keep mentioning it I haven't seen you acknowledge > using EXPLAIN QUERY PLAN. It would be nice to hear if it turns out to be > helpful especially as it doesn't require mucking with the query itself. Yes it was a good

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/11 15:52, Simon Slavin wrote: > But I was sure I had read that in SQLite the full result set was > retrieved even if LIMIT was used. For the outer query all LIMIT does is cause sqlite3_step to return done after limit number of calls. ie you

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Nico Williams
On Fri, Oct 7, 2011 at 5:52 PM, Simon Slavin wrote: > Okay, I understand why defining an ORDER BY requires the entire result set to > be retrieved.  I had intended to remove ORDER BY when I used COUNT(*), though > I didn't mention that. If the ORDER BY can be satisfied

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Kees Nuyt
On Fri, 7 Oct 2011 19:57:09 +0100, Simon Slavin wrote: >On 7 Oct 2011, at 7:17pm, Roger Binns wrote: > >> On 07/10/11 09:52, Simon Slavin wrote: >>> "Do you really want to see all 50,000 entries that that search would >>> return ?". If this kind of search returns more than

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Simon Slavin
On 7 Oct 2011, at 10:27pm, Roger Binns wrote: > On 07/10/11 11:57, Simon Slavin wrote: >> My problem really seems to be with the way SQLite implements LIMIT n. >> If I understand previous posts on this list correctly, instead of >> finding only the first n records, it does the entire search

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/11 11:57, Simon Slavin wrote: > My problem really seems to be with the way SQLite implements LIMIT n. > If I understand previous posts on this list correctly, instead of > finding only the first n records, it does the entire search first,

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Nico Williams
On Fri, Oct 7, 2011 at 1:17 PM, Roger Binns wrote: > On 07/10/11 09:52, Simon Slavin wrote: >> "Do you really want to see all 50,000 entries that that search would >> return ?".  If this kind of search returns more than 100 records, >> there's no point in doing it at all. >

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Simon Slavin
On 7 Oct 2011, at 7:17pm, Roger Binns wrote: > On 07/10/11 09:52, Simon Slavin wrote: >> "Do you really want to see all 50,000 entries that that search would >> return ?". If this kind of search returns more than 100 records, >> there's no point in doing it at all. > > You can solve this at

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/11 09:52, Simon Slavin wrote: > "Do you really want to see all 50,000 entries that that search would > return ?". If this kind of search returns more than 100 records, > there's no point in doing it at all. You can solve this at the user

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread David Garfield
Puneet Kishor writes: > Especially, note Pavel's recent, wonderful explanation of how SQLite steps > through the result set handing you the data row by row. It *has* to go > through the set to know how big the set is... there is no way around it. > Actually, sometimes there are. Consider:

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Puneet Kishor
On Oct 7, 2011, at 11:52 AM, Simon Slavin wrote: > > On 7 Oct 2011, at 5:31pm, Puneet Kishor wrote: > >> assuming you have some other application level language you are using to get >> the data, you could stuff the result set into an array and then report the >> highest index of the array

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Simon Slavin
On 7 Oct 2011, at 5:31pm, Puneet Kishor wrote: > assuming you have some other application level language you are using to get > the data, you could stuff the result set into an array and then report the > highest index of the array which might be faster than doing a second query > for just

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Puneet Kishor
On Oct 7, 2011, at 9:46 AM, Simon Slavin wrote: > > On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote: > >> SQLite has a non-standard extension whereby aliases assigned to expressions >> in the SELECT clause may be used in the WHERE and other clauses: >> >> select 1+2 as alias from mytable

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Simon Slavin
On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote: > SQLite has a non-standard extension whereby aliases assigned to expressions > in the SELECT clause may be used in the WHERE and other clauses: > > select 1+2 as alias from mytable where alias > 0; Arg ! Okay, so I guess the form >

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread David Garfield
WHERE or HAVING clauses that refer back to named results could be a problem with the simple replacement. SELECT a,b,a+b AS ab FROM t WHERE ab>10 Igor's suggestion work there too. --David Garfield Simon Slavin writes: > I'm trying to write some code which has to be useful under many

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Simon Slavin
On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote: [useful stuff] Thanks Igor. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Igor Tandetnik
Simon Slavin wrote: > I'm trying to write some code which has to be useful under many different > circumstances. Something I want to be able to do is to > take an arbitrary SELECT statement and replace the columns which would > normally be returned with COUNT(*) to find

[sqlite] Using COUNT(*) in unusual circumstances

2011-10-07 Thread Simon Slavin
I'm trying to write some code which has to be useful under many different circumstances. Something I want to be able to do is to take an arbitrary SELECT statement and replace the columns which would normally be returned with COUNT(*) to find out how many rows would be returned. To do this I