Re: How do I browse a database ?

On Wed, 7 Aug 2019 12:26:19 +0200, "R.Wieser" <address_at_not.available> wrote:

> Hello all,
>
> Some time ago I tried to create a program to browse the contents of a random
> SQL database.
>
> The problem is that I could not find a dependable* method to get the next or
> previous screen full of records - while keeping resource-usage (for both the
> database as well as the program) low**.
>
> Is there a standard way to do it, and if so what is it*** ?

[Quoted] The standard way is _not_ to use OFFSET for remembering where you are.
OFFSET means the resultset has to be built from row 1 up to and including OFFSET + LIMIT, skipping the rows before OFFSET only in the final presentation.

In case of ORDER BY (that is, always) that often means all rows in the resultset have to be retrieved.

[Quoted] Better use some unique key (single column or a composite key, consisting of several columns) to remember where you are.

SELECT ....
  FROM ....
 WHERE [that key] > [saved key] ....
ORDER BY [that key] LIMIT [number of rows you want at a time]

Save the first and the last key of each resultset: - the first to "page backward"
- the last to "page forward"

Not easy, but much faster.
I'm sure you can work out the details by yourself, I don't have any sample code at hand at the moment.

HTH

-- 
Regards,
Kees Nuyt