Re: [HACKERS] What about LIMIT in SELECT ?

On Tue, 13 Oct 1998, Eric Lee Green wrote:

> On Tue, 13 Oct 1998, Jeff Hoffmann wrote:
> > >I agree completely, LIMIT would be VERY usefull in web based apps, which
> > >is all I run.  It does not matter to me if it is not part of a formal
> > >standard.  The idea is so common that it is a defacto standard.
> >
> > i'm not familiar with mysql and using "LIMIT" but wouldn't this same effect
> > be achieved by declaring a cursor and fetching however many records in the
> > cursor?  it's a very noticeable improvement when you only want the first 20
> > out of 500 in a 200k record database, at least.
>
> The problem with declaring a cursor vs. the "LIMIT" clause is that the
> "LIMIT" clause, if used properly by the database engine (along with the
> database engine using indexes in "ORDER BY" clauses) allows the database
> engine to short-circuit the tail end of the query. That is, if you have 25
> names and the last one ends with BEAVIS, the database engine doesn't have
> to go through the BUTTHEADS and KENNYs and etc.
>
> Theoretically a cursor is superior to the "LIMIT" clause because you're
> eventually going to want the B's and K's and etc. anyhow -- but only in a
> stateful enviornment. In the stateless web environment, a cursor is
> useless because the connection can close at any time even when you're
> using "persistent" connections (and of course when the connection closes
> the cursor closes).

Ookay, I'm sorry, butyou lost me here.  I haven't gotten into using
CURSORs/FETCHs yet, since I haven't need it...but can you give an example
of what you would want to do using a LIMIT?  I may be missing something,
but wha is the different between using LIMIT to get X records, and
definiing a cursor to FETCH X records?

Practical example of *at least* the LIMIT side would be good, so that we
can at least see a physical example of what LIMIT can do that
CURSORs/FETCH can't...

Marc G. Fournier                               scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org                       ICQ#7615664