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 first, then >> returns the first n of the results it found. > > That is completely incorrect. Each time you call sqlite3_step it finds > the next row that matches the query and no more. This is why SQLite uses > that virtual machine (VDBE) stuff. It needs to execute some code to find > a matching row, return it, and then allow another call to get the next > row. This requires some way of encapsulating the processing state of a > query so it can be resumed from where it was left off in order to get the > next row. > > The one fly in the ointment is if the results are sorted. For example if > there are 10,000 result rows then there is no way to return the first 100 > without getting all result rows since doing a sort requires you have all > the data first to sort.
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. But I was sure I had read that in SQLite the full result set was retrieved even if LIMIT was used. Obviously I misremembered this. If so, very good. In that case, I can see if I have too many rows returned for everything that doesn't use a sub-SELECT or LIMIT by ... Replace everything up to the first "FROM" by "SELECT COUNT(*) ". Looking for ORDER BY and removing it and anything after it. Add my own LIMIT clause. Fortunately, the user-interface I'm using doesn't allow the use of sub-selects or LIMIT. Or UNION/INTERSECT/EXCEPT. Or DISTINCT. Probably some others I didn't think of. Thank you to everyone who has contributed to answering my question. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
