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
