Thread: I know the bad way...what is the good way?

I confess to having once written code that prints something like
"Items # 1 to 10 of 45"
by using select count(*) from t where condition; and select * from t
where condition limit 10 offset x;

I now know this is "bad", I guess because of the count() and the offset.

So what is the preferred way?  If it matters, my new application is
servlet based, so I believe (but not positive) this means cursors are an
option?  Is this a better way to do this?

Rick Schumeyer wrote:
> I confess to having once written code that prints something like
> "Items # 1 to 10 of 45"
> by using select count(*) from t where condition; and select * from t
> where condition limit 10 offset x;
>
> I now know this is "bad", I guess because of the count() and the offset.

Well, it's simple to understand, so it's good in that respect. Of
course, if someone inserts a row that matches your WHERE clause then
that can upset your counting.

> So what is the preferred way?  If it matters, my new application is
> servlet based, so I believe (but not positive) this means cursors are an
> option?  Is this a better way to do this?

You can have a scrollable cursor and page back and fore. This is only
do-able for the length of one connection of course.

You can have a table holding matches and page through that. The table
can hold all columns of the results (if they need to stay the same
regardless of changes in the database) or just the foreign-keys to reach
the relevant tables. The data in the table can sit there as long as you
like of course, and be shared amongst connections.

Finally, you can cache the data outside the database. Perfectly viable
for many situations, but not good if you routinely get a million
matches. Excellent for lists like "today's stories" that many clients
want and are reasonably sized.

Or, if you don't have that much activity, your "bad" approach is
perfectly fine of course.
--
   Richard Huxton
   Archonet Ltd

On Fri, 2006-11-03 at 09:06 -0500, Rick Schumeyer wrote:
> I confess to having once written code that prints something like
> "Items # 1 to 10 of 45"
> by using select count(*) from t where condition; and select * from t
> where condition limit 10 offset x;
>
> I now know this is "bad", I guess because of the count() and the offset.
>

It's not that bad. You run the risk of getting inconsistent results if
the table is modified while the user is browsing between pages.

For instance, if someone views page one and gets records 0-9, and
someone else deletes the 3rd record, the 10th record will then move to
the first set of 10 results, and when the user clicks "next" he will get
11-20, and will never see 10. For some applications this matters, for
some it doesn't.

> So what is the preferred way?  If it matters, my new application is
> servlet based, so I believe (but not positive) this means cursors are an

As long as you make sure not to leave an open transaction waiting for
user input. You can declare a cursor "WITH HOLD" which keeps it
available for that connection, but that doesn't seem like the best
answer in this case.

> option?  Is this a better way to do this?

If the number of total results is somewhat small, run the query and
cache the primary keys in the session data. If a record gets deleted,
just show fewer results on that page, but keep any given result
consistently on the same page to prevent user confusion.

It really depends a lot on what you want to happen, and what is least
confusing to your users. Browsing a list of products for sale you would
want to be more accurate and up-to-date, so you want to ORDER BY a
timestamp or something, and then that way as the user browses any new
products are added to the end.

Regards,
    Jeff Davis