[kronolith] performance on large systems

[Chuck Hagenbuch]

Quoting John Madden <jmadden at ivytech.edu>:

>> This is the open bug:
>> http://bugs.horde.org/ticket/7363
>>
>> If you have optimized SQL queries for PostgreSQL, it would be great  
>> to  get this sorted once and for all.
>
> I'm thinking about shoe-horning this into the kronolith code:
>
> SELECT DISTINCT s.* FROM kronolith_shares s WHERE s.share_id IN
>
> (SELECT share_id FROM kronolith_shares_groups WHERE group_uid IN  
> (SELECT group_uid FROM horde_groups_members WHERE  
> user_uid='jmadden') AND perm & 2 <> 0)
>
> OR s.share_id IN
>
> (SELECT s.share_id FROM kronolith_shares s WHERE s.share_id IN  
> (SELECT share_id FROM kronolith_shares_users WHERE  
> user_uid='jmadden' AND perm & 2 <> 0))
>
> OR s.share_id IN
>
> (SELECT s.share_id FROM kronolith_shares s WHERE s.share_owner='jmadden');
>
> In my testing so far that's producing the same output as the stock  
> query but it's doing so in ~65ms instead of ~1200ms.  Obviously I'd  
> have to fit that to match the string substitutions, but other than  
> that, would the above work?

You said you're using Postgres, right? If that's fast for postgres  
that's great; I haven't tried it yet (I'll try your patch later), but  
the subqueries seem like they'd perform poorly with MySQL...

-chuck