[Tickets #7363] share sql driver performance

[bugs at horde.org]

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

Ticket URL: http://bugs.horde.org/ticket/7363
------------------------------------------------------------------------------
  Ticket             | 7363
  Created By         | leena.heino at uta.fi
  Summary            | share sql driver performance
  Queue              | Horde Framework Packages
  Version            | FRAMEWORK_3
  Type               | Enhancement
  State              | New
  Priority           | 2. Medium
  Milestone          |
  Patch              |
  Owners             |
------------------------------------------------------------------------------


leena.heino at uta.fi (2008-09-19 13:12) wrote:

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.