Thanks! I have tested your solution and it worked like a magic! I need do more investigation to see whether it can be applied in my case.
Thank you. On 2月4日, 下午11时25分, Lizard Lizard <[email protected]> wrote: > On Fri, Feb 4, 2011 at 10:11 AM, Steve Luo <[email protected]> wrote: > > Hi, I have a simple table which contains one million rows and when > > executing below query: > > select * from foo limit 50 offset 900000 > > it needs about 14-15 seconds, instead, if executing query: > > select * from foo limit 50 offset 100 > > it only costs about 20 milliseconds. > > > I test it with h2 1.3.150 and using embedded mode. The table foo is > > fairly simple: > > create table test ( ID LONG NOT NULL AUTO_INCREMENT PRIMARY KEY, > > FILEPATH VARCHAR, FILENAME VARCHAR, UPLOAD TIMESTAMP) > > > By the way, whether I use order by ID or not, the result is same. > > Can some one tell me why and how to optimize this? Thanks in advance. > > I experience this a lot; my guess is that it needs to read linearly to > the offset and consumes a huge amount of system resources in the > process. I worked around it by adding a sequential index and using > that, so, intead of "offset 900000", I use "select * from foo limit 50 > where index>900000". Obviously, this solution isn't applicable in all > situations. > > -- > ======================= > Personal Blog:http://www.xanga.com/lizard_sf > Facebook:http://www.facebook.com/lizard_sf > MrLizard: Gaming and Geekery:http://www.mrlizard.com -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
