Tickets :: Comment on [#7363] Avoid bitwise operations in the SQL Share driver

* Your Email Address
* Spam protection	Enter the letters below: __ __..__ .___..___. / `(__ [__) \| \| \__..__)\| \ \| \|
Comment	> 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.
Attachment
Watch this ticket

Saved Queries