Re: [sqlite] OFFSET Performance
Fabian wrote: > 2011/10/12 Igor Tandetnik > >> >> See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > > I tried to implement the method as suggested in the article, but it will > only work for pagination where the user is only allowed to go 1 page back or > 1 page forward (since you have to remember the last rowid). In my case, the > user is allowed to jump to the last page, without visiting any of the > previous pages, making the suggested method impossible to implement. To get to the last page, you could reverse the ORDER BY, effectively reading backwards. Of course you'll have to reverse again in the application code, for presentation. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OFFSET Performance
2011/10/12 Igor Tandetnik > > See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor I tried to implement the method as suggested in the article, but it will only work for pagination where the user is only allowed to go 1 page back or 1 page forward (since you have to remember the last rowid). In my case, the user is allowed to jump to the last page, without visiting any of the previous pages, making the suggested method impossible to implement. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OFFSET Performance
2011/10/12 Igor Tandetnik > > See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor Thanks! Very interesting! I already was aware that using a large OFFSET could potentially be slow, because SQLite internally reads all preceding rows, and just discards them. But I do my offsets exclusively on rowid, and it appears there is some optimization taking place, since I can specify very large offsets, without a change in performance. So I assumed that SQLite is taking some shortcut when using rowid for OFFSET, and I was only wondering why this 'shortcut' is disabled when joining a FTS table. I'm going to implement the method described in the above article, and see if it makes any differences. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OFFSET Performance
Fabian wrote: > This query returns the results as expected, and performs well. But as soon > as I raise the OFFSET to a large value (for pagination) the performance > drops drastically. See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users