[rt-users] query drastically slowing down RT

[Jeff Fearn]

On 04/30/2012 10:23 AM, Glyn Davies wrote:
> Hi Folks,
>
> I have problems with a couple of user generated queries that drastically
> slow down the RT database.
>
> The database is MySQL 5 running on a separate server from RT ( 4.0.5 )
>
> The queries in question are of a similar format to the following,
>
> Queue = 'Solutions'
> AND (  Status = 'new' OR Status = 'open' )
> AND (  Requestor.EmailAddress = 'user1 at site.net.nz' OR
> Requestor.EmailAddress = 'user2 at site.net.nz' OR Requestor.EmailAddress = '
> user3 at site.net.nz' OR Requestor.EmailAddress = 'user4 at site.net.nz' OR
> Requestor.EmailAddress = 'user5 at site.net.nz' OR Requestor.EmailAddress = '
> user6 at site.net.nz' OR Requestor.EmailAddress = 'user7 at site.net.nz' )
>
> With only a few email addresses entered the query runs in an acceptable
> time, however as the number increase the query almost forces the database
> to a standstill.
>
> Does anyone have any ideas on how I can make any improvements on
> performance?
>
> Thanks in advance.
>
> Cheers,
> Glyn
>

There is a bug in the way joins are calculated by DBIx::SearchBuilder in 
some circumstances. If you log the SQL being run you will see numerous 
joins to the same table if it's the same bug.

http://issues.bestpractical.com/Ticket/Display.html?id=18414

I attached a work around to that bug, it's not complete but it works for 
us. Actually the patch was out of date as there was another side affect, 
so I've attached a newer patch to the bug.

Note that even the newer patch has the issues Ruslan talked about in the 
bug, I simply don't have time to cover all the angles required for a 
full upstream fix.

Cheers, Jeff.

-- 
Jeff Fearn <jfearn at redhat.com>
Senior Software Engineer
Red Hat