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

fetch with cursors should work properly (i.e., you can short circuit it by
just ending your transaction) my understanding on how this works is exactly
how you explained LIMIT to work. here's some empirical proof from one of my
sample databases:

the sample table i'm using has 156k records (names of people)
i'm using a PP180 with 128MB RAM and some old slow SCSI drives.

public_mn=> select count(*) from public_ramsey;
count
------
156566
(1 row)

i did the following query:
public_mn=> select * from public_ramsey where ownerlname ~ 'SMITH';

which returned 711 matches and took about 12 seconds.

i did the same thing with a cursor:

public_mn=> begin;
BEGIN
public_mn=> declare test cursor for select * from public_ramsey where
ownerlname ~ 'SMITH';
SELECT

the select was instantaneous.

public_mn=> fetch 20 in test;

returns 20 records almost instantaneously. each additional 20 took less
than a second, as well.

if this isn't what you're talking about, i don't understand what you're
saying.

jeff