I've got a table with about 150,000 rows, and I'm typically going to be viewing
it 4 rows at a time, based on a fairly complex multi-table join. The front end
application is web (PHP) based, and allows the user to do the "View Next 4" and
"View Previous 4" thing. Currently, the PHP code maintains the offset and
issues queries like this:
select * from table where ... offset N limit 4
My server is getting totally flogged though, and I'd like to experiment with
cursors to try and alleviate some of the load. However, since my front end is
web based, I have no control over when a user just closes the browser window
and goes away. As a result, I have no way of determining when to close the
cursor and commit the transaction. I.e., I can't declare the cursor when the
user browses the first page, and then close it when the user browses the last
page -- because I never know which page is the last one they're going to view.
It seems that I'd need to do this:
begin
declare thing cursor for select ...
move N from thing
fetch 4 from thing
close thing
commit
for every single page that gets viewed. That seems pretty silly though. So,
three questions:
1) Would this method be better than doing the "select ... offset N limit 4" for
each page?
2) What happens if a cursor is never closed / transaction is never commited?
3) Am I missing something obvious? Is there a better way to do this?
Thanks!
--
Alex Howansky
Wankwood Associates
http://www.wankwood.com/