[dev] datatree performance (again)

[Jan Schneider]

Zitat von Leena Heino <Leena.Heino at uta.fi>:

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

So would it help if we cache some of the query results during the  
users' sessions? Or is already the initial page loading inacceptable  
slow?

> 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

That's wrong. "using where" and "using filesort" are the fastest  
expectable operations when doing queries with WHERE and ORDER  
statements. "Using temporary" is also expected because we use  
different column orders in the ORDER BY and GROUP BY lists. Perhaps we  
could achieve the same results without requiring temporary by  
reordering the columns though.

Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/