Re: [sqlite] OFFSET Performance

[Igor Tandetnik]

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

[Fabian]

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

[Fabian]

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

[Igor Tandetnik]

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