Hi, > After some more experimentation, it seems that selects slow down the > further I get into the dataset. For example "select * from > training.ratings limit 10 offset x". With x = 1000, it returns the > rows immediately. x=10000 takes a second or two. x=10000 takes maybe > 10 seconds. x=1000000 never seems to return.
Such queries are not optimized. The query will process all rows, and then ignore the beginning x rows. I'm not sure if there are many databases that do optimize such queries. The LIMIT is usually used for paging. 10 seconds sounds like a lot however. Is it possible in your case to use an index for paging? Example (@LOOP works in the H2 Console only): drop table ratings; create table ratings(customer_id int, book_id int, rating real, primary key(customer_id, book_id)); @LOOP 10000 insert into ratings values(? / 14, mod(?, 14), rand() * 10); select * from ratings order by customer_id, book_id limit 10; select * from ratings where (customer_id, book_id) > (0, 9) order by customer_id, book_id limit 10; select * from ratings where (customer_id, book_id) > (1, 5) order by customer_id, book_id limit 10; select * from ratings where (customer_id, book_id) > (2, 1) order by customer_id, book_id limit 10; Regards, Thomas --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
