On Sun, Jun 20, 2010 at 8:55 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>> 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?
Pavel, I am not telling my opinion, but I am also not 100% sure. As I
wrote, if I recall correctly, that is how LIMIT works. I remember
reading something to that effect. That said, I could very well be
wrong.
> 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.
The above brings up a related issue... At least Pg docs suggest that
LIMIT should always be used with ORDER BY. See
http://developer.postgresql.org/pgdocs/postgres/sql-select.html
"When using LIMIT, it is a good idea to use an ORDER BY clause that
constrains the result rows into a unique order. Otherwise you will get
an unpredictable subset of the query's rows — you might be asking for
the tenth through twentieth rows, but tenth through twentieth in what
ordering? You don't know what ordering unless you specify ORDER BY."
Which implies, the WHERE clause will be applied, then the ORDER BY
will be applied on the entire set (because the returned set might not
be in the desired order), and then, LIMIT will be applied. So, that in
turn implies that by the time LIMIT is applied, the result set is
already out of the bag, it has already been calculated.
In any case, I don't have a definitive citation for my belief, so I am
happy to be corrected.
>
>
> 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
>
--
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
