[dev] datatree performance (again)

[Leena Heino]

>> What exactly is the problem with this query? The number of entries in
>> the datatree tables, or the number of queries per time? What happens
>> if you run the query on the system while it has no load?

In our system the problem was caused by the number of queries and time it 
takes to complete a single DataTree related query. Once the queries 
started to take a couple of seconds of wall clock time to complete then 
the backlog of queries started to grow and performance went downhill from 
there.

Using MySQL's explain to a normal DataTree query:
EXPLAIN SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT 
JOIN horde_datatree_attributes a1 ON a1.data
t = 'xxxxxxxx') OR (a1.attribute_name = 'perm_users' AND 
a1.attribute_key = 'xxxxxxxx' 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;

Output of explain's extra column shows that MySQL has to use operations 
that are known to be expensive in terms of performance: Using where, Using 
temporary, Using filesort

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