[Building Sakai] Proposed API change to reduce cost of resources list view

[Stephen Marquard]

Happily that does seem to be the case for mysql as well (which is usually worse than Oracle in index-related optimizations), e.g.

mysql> explain SELECT * FROM CONTENT_RESOURCE WHERE RESOURCE_ID LIKE '/group/%';
+----+-------------+------------------+-------+------------------------+------------------------+---------+------+--------+-------------+
| id | select_type | table            | type  | possible_keys          | key                    | key_len | ref  | rows   | Extra       |
+----+-------------+------------------+-------+------------------------+------------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | CONTENT_RESOURCE | range | CONTENT_RESOURCE_INDEX | CONTENT_RESOURCE_INDEX | 767     | NULL | 375212 | Using where | 
+----+-------------+------------------+-------+------------------------+------------------------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM CONTENT_RESOURCE WHERE RESOURCE_ID LIKE '/group/!admin/%';
+----+-------------+------------------+-------+------------------------+------------------------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys          | key                    | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+------------------------+------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | CONTENT_RESOURCE | range | CONTENT_RESOURCE_INDEX | CONTENT_RESOURCE_INDEX | 767     | NULL |   72 | Using where | 
+----+-------------+------------------+-------+------------------------+------------------------+---------+------+------+-------------+
1 row in set (0.46 sec)

Cheers
Stephen
 
>>> "Holladay, Bryan Andrew" <bahollad at indiana.edu> 9/22/2009 7:48 PM >>> 
Right sided like searches are still able to use the DBMS's indexes.  They are still really quick.  So I think this is a good idea.

Bryan


On 9/22/09 1:43 PM, "Mark Norton" <markjnorton at earthlink.net> wrote:

My understanding is that SQL statements involving "like" are not very
efficient, either.  Are you trading one problem for another?

- Mark

Jim Eng wrote:
> The list view in the Resources tool in Sakai 2.x has a column to show
> who can see each resource or folder. Potential values include the
> entire site, members of particular groups, and the public.
>
> Items at any level may be made public, so the question ("is this item
> public?") must be asked for virtually every item in a site.
> Determining whether resources and folders are open to the public is
> relatively expensive because it must be repeated so often.  If a site
> contains lots of resources, the expanded view can results in dozens,
> hundreds or thousands of queries.  The only short cut at this time is
> that the query can be avoided for items that are inside a folder that
> is public since they will also be public.
>
> The SQL query is against the various realms tables.  It asks whether
> the anonymous role has the "content.read" permission for the realm
> associated with a particular resource or folder (or for the realm of
> any folder that contains the item).
>
> We could improve on that if the SecurityService (or
> AuthzGroupService?) interface had a method to ask a different
> question: Are there any realms within this site for which the
> anonymous role has "content.read" permission (and if so, what are they)?
>
> This query would likely be implemented with a SQL "like" operator and
> a one-sided wildcard. This is actually a join of information from
> three or four tables, but the query would be asking for a list of
> sakai_realm.realm_id values like '/content/group/<side-id>/%' (or like
> '/content/group-user/<user-id>/%' for workspaces, and it's possible
> there are other variations) for which there are entries in the
> sakai_realm_rl_fn table where the role_key identifies the anonymous
> role and the function_key corresponds to "content.read". The result
> would be a list of realm identifiers.
>
> That would allow Content Hosting to get a list of realms (possibly
> empty) for items that are public.  Content Hosting could then avoid
> making dozens or hundreds or thousands of individual queries by
> checking that list of public realms.  An alternative to adding a
> specific API method for this would be to execute that query in the
> SecurityService and do some form of caching so the Security Service
> could answer this question hundreds of times without doing hundreds of
> individual queries.
>
> Thoughts?
>
> Jim
>