Page Through Database with Limit and Offset

Answered

Currently, when paging through a database view in DataGrip, the query `SELECT t.* from <table> t LIMIT (500*<page count>)` is used to select the next page.

When going more than a couple pages forward in a table, the queries become slower and slower. My initial query took 2 seconds, but the query for page 5 took 8 seconds.

The paging query should incorporate the OFFSET command as follows (assuming page count starts at 1):

SELECT t.* from <table> t LIMIT (500*<page count>) OFFSET (500*(<page count> - 1))

 

1 comment
Comment actions Permalink

There is an issue https://youtrack.jetbrains.com/issue/DBE-6351 with similar behaviour. 

0

Please sign in to leave a comment.