On Sun, Jun 20, 2010 at 7:53 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 21 Jun 2010, at 12:41am, P Kishor wrote: > >> iirc, LIMIT 1 is applied *after* the WHERE clause is satisfied. In >> other words, the entire result set is returned, and then it is >> LIMITed. So, the behavior is correct. > > That does agree with what Sam is reporting. However, I am surprised at this > fact. I have been assuming for years that using LIMIT saves the SQL engine > from having to list all the possible entries. Having used a number of other > SQL implementations over the years it didn't even occur to me to check to see > how LIMIT was implemented in SQLite. I'm sure I'm not the only person who > uses LIMIT assuming it will reduce CPU and memory to a small limited amount. >
I think that is a common assumption, but a wrong one. Think about it -- the sql engine has to get the entire result set back before it can apply the limit clause. But, I could be wrong. Maybe other db engines do it differently. In any case, that is the reason there is the suggested scrolling cursor document on sqlite.org, to help folks with the common use-case of paging through results on, say, a web page. > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
