[Tickets #3046] Move permissions data to its own table

[bugs@bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/?id=3046
-----------------------------------------------------------------------
 Ticket             | 3046
 Updated By         | luc.germain at usherbrooke.ca
 Summary            | Move permissions data to its own table
 Queue              | Horde Framework Packages
 Version            | FRAMEWORK_3
 State              | Feedback
 Priority           | 3. High
 Type               | Enhancement
 Owners             | 
-----------------------------------------------------------------------


luc.germain at usherbrooke.ca (2005-11-28 09:09) wrote:

> The permission system was one of the main reasons for writing the 
> DataTree backend, and we need a tree structure for permissions. Also, 
> now that history has been moved out, permissions is (almost) the only 
> thing left that uses DataTree.

When we got our performance problem, history was not activated, so moving
out the history would not be enough. The performance problem with the actual
permission implementation arise when there are many users. Our database
server came to a halt after about 4000 users has logged in. 

We found that these type of requests were the main performance problem:

SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT JOIN
horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id WHERE
c.group_uid = 'horde.shares.nag' AND ((a1.attribute_name = 'owner' AND
a1.attribute_value = 'myuserid') OR (a1.attribute_name = 'perm_users'
AND a1.attribute_key = 'myuserid' AND a1.attribute_value &
2) OR (a1.attribute_name = 'perm_creator' AND a1.attribute_value & 2) OR
(a1.attribute_name = 'perm_default' AND a1.attribute_value & 2))
GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY
c.datatree_order, c.datatree_name, c.datatree_id

When doing an "explain" on the mysql server, it shows that this request has
to evaluate each time a number of entries roughly proportionnal to the
number of users in the database (since each user has at least one share for
each type). If each type of shares had its own table (horde.shares.nag,
horde.shares.kronolith), it would not have to do the left join and generate
a temporary table each time, and it would probably better use the indexes. 

If you have another way (other than changing the database structure or use
session caching) to improve the performance of the permission system with
thousands of users, I'm all ears!  Right now,we are stucked with horde 2.2
because of this problem :(