Well, as long as you don't mind the overhead, you could simply prevent the table from changing as you page by locking the table while you're paging through it:
LOCK TABLE table READ; SELECT ... LIMIT o,n; ... UNLOCK TABLES; as long as the paging process doesn't take too long and/or the update rate is pretty low, this shouldn't be a problem. You could simply grab as many pages as you wish to show into local memory and display the data out of there instead of directly out of the database. Assumes some local storage you can use across HTTP requests, of course. james montebello On Fri, 11 Jan 2002, Bret Ewin wrote: > Thank you! I can use the LIMIT option several places in the system. However, > in places where a changing result set is not acceptable I must read the > whole result set (or at least as many as I allow pagination across) in the > initial query. I tried Statement.setFetchSize() but that doesn't appear to > help. Any suggestions in this area? > > Thanks, > Bret > > -----Original Message----- > From: James Montebello [mailto:[EMAIL PROTECTED]] > Sent: Friday, January 11, 2002 5:57 PM > To: Bret Ewin > Cc: [EMAIL PROTECTED] > Subject: Re: returning partial result set > > > > If all you want is the first page, then simply use SELECT ... LIMIT n, > which will only return the first n rows of the set. To "page through" > data, use LIMIT offset, n, which will return n rows starting at offset > rows into the dataset. Note that doing this on a changing table will > give results that may not be useful, like skipped or duplicated rows > between "pages". > > james montebello > > On Fri, 11 Jan 2002, Bret Ewin wrote: > > > My system tends to generate large result sets, with the first page of > > results being used and the rest thrown away. I expect this is typical in > > other systems as well. I've noticed the JDBC driver retrieves the entire > > result set before letting the application iterate over any results. I'm > not > > sure if this is a limitation in the database or the driver. > > > > Oracle has the capability to start returning results as they are > generated. > > Does MySQL have something similar? If not, is there anything I can do to > > alleviate this behavior? > > > > Thanks, > > Bret > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
