Re: [HACKERS] What about LIMIT in SELECT ?

On Tue, 13 Oct 1998, Marc G. Fournier wrote:
> On Tue, 13 Oct 1998, Eric Lee Green wrote:
> > Whoops! Sorry, I goofed in my post (typing  faster than my brain :-).
> > What I *MEANT* to say was that this superiority of cursors was not
> > applicable in a web environment.
>
>     S'alright...now please backup your statement with the *why*...

Okay. It is because CGI is a stateless environment. You cannot just keep a
cursor open and walk up and down it, which is the superiority of cursors
(it is always faster to walk up and down a pre-SELECT'ed list than it is
to perform additional SELECTs). You have to destroy it upon exiting the
CGI script (which presumably just fetched 25 items or so to display on
an HTML page -- think DejaNews).

Creating a cursor and destroying a cursor take time. Less time, in a
normal environment, than it would take to make multiple SELECT statements,
which is the superiority of cursors in a normal environment. But, like I
said, CGI isn't normal -- the CGI script exits at the end of displaying 25
items, at which point the cursor is destroyed, thus destroying any benefit
you could have gotten while adding additional overhead.

In addition there is the possibility of short-circuiting the SELECT if
there is a LIMIT clause and there is no ORDER BY clause or the ORDER BY
clause is walking down an index (the later being a possibility only if
there is no 'join' involved or if the 'join' is simple enough that it can
be done without running afoul of the join optimizer). Cursors, by their
nature, require performing the entire tamale first.

> > >From a logical point of view, none. From an implementation point of
> > view, it is a matter of speed. Declaring a cursor four times, doing a
>
> already and stated that, for him, CURSOR/FETCH results were near
> instantaneous with a 167k+ table...have you tested the two to ensure that,
> in fact, one is/isn't faster then the other?

CURSOR/FETCH *SHOULD* be nearly instantaneous, because you're merely
fetching values from a pre-existing query result. As I said, in normal
(non-CGI) use, a cursor is FAR superior to a "LIMIT" clause.

But the question of whether declaring a cursor four times and destroying
it four times takes a sizable amount of time compared to a LIMIT
clause... it's really hard to test, unfortunately, due to the differing
natures of MySQL and PostgreSQL. MySQL starts up a connection very fast
while PostgreSQL takes awhile (has anybody done work on the "herd of
servers"  concept to tackle that?). It is hard, in a CGI environment, to
detirmine if the poor speed (in terms of number of hits the server can
take) is due to the slow connection startup time or due to the cursor
overhead. I could write a benchmark program that kept the connection open
and did just the cursor timings, but I'm not particularly motivated.

I think RMS has a point when he decries the fact that non-free software is
becoming more available for Linux (MySQL is definitely non-free)... i.e.,
that it takes away people's motivation to improve the free software. The
only good part there is that MySQL is hardly suitable for normal database
work -- it is very much optimized for web serving and other applications
of that sort where speed and CGI-friendliness are more important than
functionality.

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