[horde] share sql driver and bitwise operations

[Leena Heino]

Hello,

It seems that these bitwise operations in share sql-driver is a major 
performance killer at least in MySQL. This seems to be because SQL's 
bitwise operations do not use index in MySQL and therefore each query made 
in share sql-driver has to make a full table scan.

Would it be possible to change the database schema and the code so that 
each permission to different user type (creator/default/guest) would be 
stored in its own column and therefore we would have columns like 
perm_creator_show, perm_creator_read, perm_creator_edit and 
perm_creator_delete. This would allow us to ditch the bitwise operations 
in the query.

Instead of current query like this:
SELECT DISTINCT s.*  FROM nag_shares s
LEFT JOIN nag_shares_users AS u
ON u.share_id = s.share_id WHERE s.share_owner = 'foo'
OR (s.perm_creator & 4) != 0
OR (s.perm_default & 4) != 0
OR ( u.user_uid = 'foo' AND (u.perm & 4) != 0)
ORDER BY s.attribute_name ASC;

we could do the same query as:
SELECT DISTINCT s.*  FROM nag_shares s
LEFT JOIN nag_shares_users AS u
ON u.share_id = s.share_id WHERE s.share_owner = 'foo'
OR (s.perm_creator_read = 1)
OR (s.perm_default_read = 1)
OR ( u.user_uid = 'foo' AND (u.perm_read = 1))
ORDER BY s.attribute_name ASC;

Or maybe rewriting the query using subquery and union:
SELECT DISTINCT * FROM ((SELECT s.* FROM nag_shares s
WHERE (s.perm_creator = 1)
OR (s.perm_default = 1)
OR (s.share_owner = 'foo')) 
UNION (SELECT s.* FROM nag_shares s
LEFT JOIN kronolith_shares_users 
AS u ON u.share_id = s.share_id WHERE (u.user_uid = 'foo'
AND (u.perm = 1)))) AS new_s ORDER BY new_s.attribute_name ASC;

Perhaps gaining a better utilization of index in the database queries.

-- 
   Leena Heino              University of Tampere / Computer Centre
   ( liinu at uta.fi )      ( http://www.uta.fi/laitokset/tkk )