[dev] datatree performance (again)

[Jan Schneider]

Zitat von Didi Rieder <adrieder at sbox.tugraz.at>:

> Hi,
>
> as far as I know I disabled all shareing prefs for turba, nag and kronolith.
> IMP also doesn't use tasks.
> But my database retuns over 32,000 rows for each the folowing queries:
>
> select count(*) from horde_datatree where horde_datatree.group_uid =
> 'horde.shares.nag';
>
> select count(*) from horde_datatree where horde_datatree.group_uid =
> 'horde.shares.kronolith';
>
> And I see many queries of the following type:
>
> 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 = 'misch at sbox'
> )
> OR (
> a1.attribute_name = 'perm_users'
> AND a1.attribute_key = 'misch at sbox'
> 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;
>
> 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.nag'
> AND (
> (
> a1.attribute_name = 'owner'
> AND a1.attribute_value = 'xxx at xxx'
> )
> OR (
> a1.attribute_name = 'perm_users'
> AND a1.attribute_key = 'xxx at xxx'
> 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;
>
> Those queries take about 8 seconds to complete and are bringing the
> MySql server to its knees.

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?

Jan.

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