> 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.
Puneet, are you 100% sure about that or are you just telling your
opinion? Just recently there was another thread where "strange"
behavior was very well explained by the fact that SQLite stopped
processing query with LIMIT 1 in it after it returned first row. Even
if I do what you ask ("Think about it -- the sql engine has to get the
entire result set back before it can apply the limit clause") I don't
see why is that? I can agree that if you put ORDER BY ... LIMIT 1 and
you don't have any index satisfying ORDER BY clause then indeed sql
engine have to take the whole result set, sort it and then return the
first row. But in all other cases I don't see why it should do that
and in fact I think SQLite smart enough to not do that.
Pavel
On Sun, Jun 20, 2010 at 8:56 PM, P Kishor <punk.k...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
