| Re: Record pagination with count of total rows [message #170504 is a reply to message #170502] |
Wed, 03 November 2010 14:09   |
Matthew Leonhardt
Messages: 9
Registered: November 2010
Karma:
|
Junior Member |
|
|
"Jerry Stuckle" <jstucklex(at)attglobal(dot)net> wrote in message
news:iarnmg$rar$1(at)news(dot)eternal-september(dot)org...
> 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
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Perhaps I should have clarified. Not often.
>>> 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.
No I didn't. I was specifically addressing his question about multiple
users executing identical queries.
> And if you have multiple people running concurrently, increase the db
> buffer accordingly.
Agreed, but OT.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]