| Re: Record pagination with count of total rows [message #170503 is a reply to message #170501] |
Wed, 03 November 2010 13:55   |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma:
|
Senior Member |
|
|
On Wed, 3 Nov 2010 08:14:00 -0400, 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.
I can understand that, but you've got one problem, not a problem of
code, a problem of db layout, and a problem of db server config that are
separate. What you do in one may/will affect what you do for the others.
>> 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).
This smells like trouble too. At least such that the security that it
may afford is likely to adversely affect performance. But that'd require
a lot of detail about precisely what you're limiting from whom and with
which and by how. Mostly, people are unwilling to share that kind of
information.
> 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.
It depends how and where you've flattened the data, and how it's
indexed. [hypothetical] If your access is determined by a record code
that links to a table of values that are used as a bitwise array to
check against specific flags in a user-record bitwise array, then yes,
it's going to be horrible. If you're using a float-value to describe the
"sensitivity" of a record and comparing that to another float value,
that's going to suck too. If you've got a massive intersection table
between your data and your user records, that caches poorly if the whole
intersection table doesn't fit in the cache and you've got multiple
users fussing over the table at the same time, but may work just fine if
there's usually only one person grovelling over the list of records at a
time. Similarly if you've got nearly unique views over the data for each
user, limiting which records they can see, and also have more than one
working at the same time. Some of those scenarios may sound silly, but
every one of them is a highly-granular permissions scheme that's been
proposed to me at one point or another in the past. ... And, of course,
there's the factor that most of the time, tracing back the development
of the very granular user permissions ended up showing that the problem
and requirements were very different from the scheme under
consideration.
--
When you have a thermic lance, everything looks like hours of fun.
-- Christian Wagner in a.s.r
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]