On Wed, 2011-02-09 at 13:27 +0200, Arno Kuhl wrote:
> Instead of serializing the articles, you only need their IDs. Using
>
> $sql .= ' where id in (' . implode(',', $ids) . ')';
>
> you can load the data for a page of results in a single query. Storing the
> IDs is much cheaper than the articles.
>
> If the permissions are fairly static (i.e. access for user X to article Y
> doesn't change every two minutes) you could create a calculated permission
> table as a many-to-many between user and article. Here's the logic flow for
> a query:
>
> 1. Run the query to find matching article IDs
> 2. Load permissions from table for all IDs
> 3. For each article without a calculated permission, calculate it and insert
> a row (do a batch insert to save time)
>
> If you flag the query in the middle tier as having been processed as above,
> you can join to the calculated permissions each time you need another page.
> The downside is that the code that runs the queries has to operate in two
> modes: raw and joined to the permissions. If most users end up querying for
> all articles, the table could grow. Plus you need to purge rows any time the
> permissions for an article/user changes which could get fairly complicated.
>
> David
>
> -----------
>
> Storing only the IDs is way cheaper than storing the entire resultset, and
> I'd been thinking along the same lines. Getting a complete list of valid IDs
> in the first place is turning out to be a different matter. The permissions
> for article/user aren't that straight-forward, and in fact the most common
> permissions are group/category and group/container, where an article can be
> assigned to one or more category/containers. Using a temporary permission
> table could be the solution. Thanks.
>
> Cheers
> Arno
>
>
>
You can get the same set of results with a join betwixt the tables, and
it should be slightly faster than creating a temporary table if you've
got your indexes right.
Thanks,
Ash
http://www.ashleysheridan.co.uk
