| Re: Record pagination with count of total rows [message #170501 is a reply to message #170494] |
Wed, 03 November 2010 12:14   |
Matthew Leonhardt
Messages: 9
Registered: November 2010
Karma:
|
Junior Member |
|
|
"Norman Peelman" <npeelman(at)cfl(dot)rr(dot)com> wrote in message
news:iaqvjv$l1s$1(at)news(dot)eternal-september(dot)org...
[snip]
> Other options to consider:
>
> MySQL - make sure that the result (query) buffer is sufficiently large
> enough to hold many such result sets so that the full scan queries run
> once and your paginated queries pull from the buffer as MySQL doesn't go
> back to disk if the result set is already buffered (unless something has
> changed). If something has changed MySQL should go back to disk for the
> result set - this should be handled by MySQL automatically.
> Using 'LIMIT offset, numrows' during pagination should dramatically
> reduce query time when the full result set is already in the buffer.
Like I said, DB server configuration is being explored in parallel. I don't
want this discussion to get off topic.
> PHP - use APC (Alternative PHP Cache) to store result sets in shared
> memory with reasonable time to live. Probably about the same as using a
> session variable but faster/less resources when multiple people doing same
> query (how often would that happen?). This could allow (with the proper
> logic) users to store/bookmark queries and come back to them without
> hitting the database again (good for queries of data that may not change).
We have our own caching mechanism in place, however, I'll check out APC. I
doubt this will help us much, however. Our permissions are very granular
and there are rarely two users that have the same permissions (and would see
the same data returned from the query). For this reason, also, MySQL's
buffers and cache don't do too much good with multiple-user scenarios--the
portion of the WHERE clause that filters results by user permissions is
almost guaranteed to be different with each user.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]