Re: Pagination - 1 or 2 queries?

Behind the scenes, is there much performance
difference between:

SELECT *
FROM table_with_millions_of_rows
ORDER BY col1;

and:

SELECT *
FROM table_with_millions_of_rows
ORDER BY col1
LIMIT 100
OFFSET 100000;

?

Wouldn't the second query would use far less memory?

CSN

--- Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> scott.marlowe wrote:
> > On Fri, 5 Sep 2003, CSN wrote:
> >
> > > Since you usually need to know the total number
> of
> > > rows a query would return, do you think it's
> better
> > > to:
> > >
> > > a) Do one query with a LIMIT and OFFSET to get
> the
> > > results, and another COUNT query to get the
> total
> > > number of rows?
> > >
> > > b) Do a single query without a LIMIT and OFFSET,
> then
> > > do a seek or similiar to get at the rows you
> want?
> > >
> > > Most tutorials, code, etc. I've seen do "a". The
> > > eclipse library does "b".
> >
> > Either way works. Does the eclipse library use a
> cursor, or grab the
> > whole dataset and then seek on the client side?
> If it uses a cursor, I'd
> > expect it to be the fastest and simplest
> implementation. Since a lot of
> > libs are designed to work with MySQL, they often
> are written in the first
> > method, where select count(*) is quite quick on
> MySQL, and MySQL doesn't
> > have cursor support.
> >
> > With Postgresql, the cursor is likely to be the
> faster method.
>
> I agree --- with a LIMIT and COUNT(*), you run the
> query twice. With a
> cursor, you run it once, and only pull the rows to
> the client you want.
>
> --
> Bruce Momjian |
> http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610)
> 359-1001
> + If your life is a hard drive, | 13 Roberts
> Road
> + Christ can be your backup. | Newtown
> Square, Pennsylvania 19073

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com