To keep connections open across requests you need to use
BMT and unshared connections with an external transaction.
You are correct this does not scale. Each client has there own unshared connection.
You will quickly run out of connections as you add more clients.
This is true for any design that links an open connection to a client across
e.g. what happens when the client goes to lunch instead of clicking "next page"
The usual technique is to either use page/size (direct support in MYSQL)
or store the primary key of the last value which you can use for subsequent queries:
When the user clicks next, you add
AND pk > pkOfLastEntryOnPreviousPage
to the query.
Thanks for the reply.
We're using PostgreSQL as our database, so limit/offset it is! We're also sorting the results returned by something other than the primary key, so the pk really has no relevance in the context that the results are being returned. However, that's a neat idea, and than you for it.
That can be done with UNIONs
... AND orderby1 = lastValue1 AND orderby2 = lastValue2 AND pk > lastPK
... AND orderby1 = lastValue1 AND orderby2 > lastValue2
... AND orderby1 > lastValue1
Or if UNIONs don't perform very well on your DB you can brute force it
running them as individual queries until the page is full.