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