[horde] share sql driver and bitwise operations

[Chuck Hagenbuch]

Quoting Leena Heino <Leena.Heino at uta.fi>:

> 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.

I think this is a good idea, and if possible we should do it for Horde  
3.3. Please create a ticket on bugs.horde.org to track it.

Also, we don't have a clear MySQL version requirement, so going to  
subqueries/union is probably not feasible yet (we should require MySQL  
4.1 or maybe 5.0+ for Horde 4), but we should explore making two  
separate queries and joining the results in PHP. With some caching,  
the simplicity of two fast queries might be better than the join.

> 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 )
> --
> Horde mailing list - Join the hunt: http://horde.org/bounties/#horde
> Frequently Asked Questions: http://horde.org/faq/
> To unsubscribe, mail: horde-unsubscribe at lists.horde.org
>



-chuck