Re: [HACKERS] What about LIMIT in SELECT ?

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).

I wanted very badly to use PostgreSQL for a web project I'm working on,
but it just wouldn't do the job :-(.

--
Eric Lee Green eric(at)linux-hw(dot)com http://www.linux-hw.com/~eric
"To call Microsoft an innovator is like calling the Pope Jewish ..."
-- James Love (Consumer Project on Technology)