| Re: Record pagination with count of total rows [message #170502 is a reply to message #170501] |
Wed, 03 November 2010 13:22   |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 11/3/2010 8:14 AM, Matthew Leonhardt wrote:
> "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.
>
>
You missed the point. When the user displays the second page, the same
query would be run - and if the db buffer is big enough, the data will
already be there, making the query very quick.
And if you have multiple people running concurrently, increase the db
buffer accordingly.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]