| Re: Record pagination with count of total rows [message #170506 is a reply to message #170504] |
Wed, 03 November 2010 16:48   |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 11/3/2010 10:09 AM, Matthew Leonhardt wrote:
> "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.
>
>
>
Yes, but the you have a performance problem involving both PHP and
MySQL. The two work together to give you your result, and your solution
will most probably involve both - so you must look at them together, not
one at a time.
--
==================
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
]