In exceptional cases, OFFSET clauses can be most efficiently implemented by counting and skipping the largest possible logical data sets in either a TABLE or INDEX structure, according to the specified SELECT query. With only one physical table in the table source and no predicates / group by expressions, etc, there are three cases:
1. SELECT * FROM t LIMIT x OFFSET y 2. SELECT * FROM t ORDER BY id LIMIT x OFFSET y 3. SELECT * FROM t ORDER BY abc LIMIT x OFFSET y The OP is asking about case 1) where OFFSET can be implemented prior to loading physical blocks from the table. In fact, the engine could just skip loading entire blocks until reaching the OFFSET y, as it knows the block size. In this example, I'd expect 1) to perform best Case 2) might perform in a similar way as 1), as the loading of table data can be skipped until the OFFSET y has been reached from the index structure, skipping index leaf blocks whose size is probably also known to the engine. Case 3) is where the current slow implementation will probably be the only reasonable implementation, as the actual data has to be traversed. Case 3) is also applicable when using more complex table sources, or predicates. Looking at H2's engine code (in particular, Select.queryFlat()), I'm not sure if such optimisations should be a priority, as I don't think that the current "execution plan" is flexible enough for such transformations. The risk of introducing bugs may be higher than the overall benefit, as the above queries are rather exceptional cases. Am Donnerstag, 11. Juli 2013 18:26:31 UTC+2 schrieb Thomas Mueller: > > Hi, > > It is possible to optimize this, but it is not so easy. I wonder how MySQL > is doing it internally. > > The best way I know to optimize it is using a "counted b-tree". This is > possible with the MVStore (the future storage engine for H2). However > support for transactions makes it more complicated, so I'm not sure if it > really makes sense to optimize it. > > Regards, > Thomas > > > On Thu, Jul 11, 2013 at 11:50 AM, pittlu <luji...@gmail.com > <javascript:>>wrote: > >> Just for write a database performance report and mysql faster than h2 >> when use similar function.Anyway thanks very much for your advice. >> >> 在 2013年7月11日星期四UTC+8下午5时30分56秒，Noel Grandin写道： >> >>> You could try using an index like this: >>> SELECT * FROM VSSQ_USER LIMIT 10 OFFSET 600000 ORDER BY ID__ >>> >>> But even that is not going to be particularly fast because it will still >>> have to iterate through the whole index to find the right position. >>> >>> The correct answer is "don't do that". >>> Why on earth on would you be trying to do that anyhow? >>> >>> On 2013-07-11 10:33, pittlu wrote: >>> > There is a table named vssq_user contains 1,000,000 records,the >>> > following SQL query takes more than 20 seconds: >>> > SELECT * FROM VSSQ_USER LIMIT 10 OFFSET 600000 >>> > >>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to h2-database...@googlegroups.com <javascript:>. >> To post to this group, send email to h2-da...@googlegroups.com<javascript:> >> . >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
