[horde] Simple Solution to 'Database performance problem with Horde3/IMP4 with a large user base'

[Akritidis Xristoforos]

The DB performance problem with the datatree permission query reported
last year has a very simple solution. All you need to do is send many
different queries instead of a single one with a bunch of OR statements
in it. mysql will not use an index if your statement has OR clauses with
the fields comprising the index. 

 

Example : 

 

The following query takes 5.65sec in my case:

 

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 = 'cakrit at hol.gr')
OR 

 (a1.attribute_name = 'perm_users' AND a1.attribute_key =
'cakrit at hol.gr' 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;

 

The reason is that it uses the wrong key (datatree_group_idx - 11760
rows). When instead I run 4 different queries for each of the OR
statements, each query takes under 0.1 sec

 

Ex: 

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 = 'cakrit at hol.gr'  

GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY
c.datatree_order, c.datatree_name, c.datatree_id;

 

This one uses the datatree_attribute_name_idx, with just one row.

 

 

So all one has to do is split the SELECT into 4 queries and join the
results. I don't know much about PHP or how Horde works, so it will be a
pain for me to provide the fix. But if someone can point me to the part
of the code that does it, I will have one of my people do it. 

 

 

 

Christopher Akritidis

Hellas On Line, Greece