Thread: Questions about LIMIT/OFFSET

I'm going to be using a smarty plugin to paginate some result sets
for display in smarty templates. I was reading that using LIMIT/OFFSET
generates multiple query plans so I'm curious if it would be better
to do a:

SELECT * FROM table WHERE foo="bar" ORDER BY abc LIMIT x OFFSET y;

or just:

SELECT * FROM table WHERE foo="bar" ORDER BY abc;

and create my result set array for my templates using application ode
- increasing the likelihood of pulling the above query from the cache?
I'm sure the answer is "it depends" but curious what others do with
this?

Thanks,

Josh

On Oct 19, 2007, at 16:03 , Josh Trutwin wrote:

> SELECT * FROM table WHERE foo="bar" ORDER BY abc LIMIT x OFFSET y;

The server will have to generate at most OFFSET + LIMIT rows,
returning LIMIT rows or fewer.

> SELECT * FROM table WHERE foo="bar" ORDER BY abc;

This will return all of the rows available.

Unless you're going to be returning all of the rows where
foo="bar" (e.g., executing multiple LIMIT OFFSET queries) in one
request, I should think the first query would be more performant:
fewer rows for the server to process (in the final step at least) and
less data transmitted between the server and your application.

Michael Glaesemann
grzm seespotcode net

On Fri, 19 Oct 2007 18:19:55 -0500
Michael Glaesemann <grzm@seespotcode.net> wrote:

>
> On Oct 19, 2007, at 16:03 , Josh Trutwin wrote:
>
> > SELECT * FROM table WHERE foo="bar" ORDER BY abc LIMIT x OFFSET y;
>
> The server will have to generate at most OFFSET + LIMIT rows,
> returning LIMIT rows or fewer.
>
> > SELECT * FROM table WHERE foo="bar" ORDER BY abc;
>
> This will return all of the rows available.
>
> Unless you're going to be returning all of the rows where
> foo="bar" (e.g., executing multiple LIMIT OFFSET queries) in one
> request, I should think the first query would be more performant:
> fewer rows for the server to process (in the final step at least)
> and less data transmitted between the server and your application.

Thanks - server and application are on the same box so not as big a
concern, but this is the way I decided to go for the time being.

Josh