[horde] Looking for a Suggestion for a SQL select change

[James MacLean]

This is an example query from horde that is causing backups on MySQL :

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.kronolith' AND ((a1.attribute_name = 'owner' 
AND a1.attribute_value = 'somone at here.com') OR (a1.attribute_name = 
'perm_users' AND a1.attribute_key = 'someone at here.com' 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


This is an example modification to using UNION which is blazingly fast :

(SELECT c.datatree_id, c.datatree_name as dt FROM 
horde_datatree_attributes a1 LEFT JOIN horde_datatree c ON c.datatree_id 
= a1.datatree_id WHERE c.group_uid = 'horde.shares.mnemo' AND 
a1.attribute_name = 'owner' AND a1.attribute_value = 'someone at here.com' 
GROUP BY c.datatree_id, dt, c.datatree_order)
UNION
(SELECT c.datatree_id, c.datatree_name as dt FROM 
horde_datatree_attributes a1 LEFT JOIN horde_datatree c ON c.datatree_id 
= a1.datatree_id WHERE c.group_uid = 'horde.shares.mnemo' AND 
a1.attribute_name = 'perm_users' AND a1.attribute_key = 
'someone at here.com' AND a1.attribute_value & 2 GROUP BY c.datatree_id, 
dt, c.datatree_order)
UNION
(SELECT c.datatree_id, c.datatree_name as dt FROM 
horde_datatree_attributes a1 LEFT JOIN horde_datatree c ON c.datatree_id 
= a1.datatree_id WHERE c.group_uid = 'horde.shares.mnemo' AND 
a1.attribute_name = 'perm_creator' AND a1.attribute_value & 2 GROUP BY 
c.datatree_id, c.datatree_name, dt)
UNION
(SELECT c.datatree_id, c.datatree_name as dt FROM 
horde_datatree_attributes a1 LEFT JOIN horde_datatree c ON c.datatree_id 
= a1.datatree_id WHERE c.group_uid = 'horde.shares.mnemo' AND 
a1.attribute_name = 'perm_default' AND a1.attribute_value & 2 GROUP BY 
c.datatree_id, c.datatree_name, dt )
ORDER BY dt...

So that's a bit extreme to go and change the code.

Originally was looking at the "copy to tmp" calls but the query was too 
slow even without GROUP and ORDER parts.

Any one have any offering of other suggestions that are closer to the 
original SQL query so that there would only be minor changes to make the 
datatree queries useable on large sites :). Tried upping many MySQL 
config parms with no speed increase on the original query. The second 
time the same original query is made, it too is very fast, but for this 
to work one needs the original query to be fast, other wise the queuing 
up and loading on MySQL starts.

JES