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.
