[rt-users] RT 4.0.8 TicketSQL issue

[Thomas Sibley]

On 02/05/2013 03:35 PM, David Good wrote:
> We have an issue with our RT 4.0.8 instance that we didn't have with
> 3.8.8 that we just finished migrating from.  Some of our managers like
> to search for tickets where the requestor is a 'VIP' user.  They use
> fairly straightforward TicketSQL to build saved searches that are then
> used for dashboards and RT at a Glance items.  This all worked fine in
> RT 3.8.8, but when we upgraded to 4.0.8 the queries take so long to run
> that it ends up timing out the webserver, making it very difficult for
> users using them in RT at a Glance to login.  Checking the MySQL slow
> query log (or just doing a 'show full processlist' while it's running)
> shows that fairly straightforward TicketSQL query gets expanded into
> ridiculously complex MySQL queries doing multiple joins on the same
> tables for the same columns.  Is this a known issue?

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

Another RT user wrote a patch which is MySQL-specific, but it's not a
clean solution (code-wise) and has some problems which make it not
something we can just ship as-is (see the ticket).

It's a straightforward query, but also seems a tad ridiculous to be
listing so many individual addresses.  As a more maintainable solution,
and a workaround to the bug, try setting the Organization of every one
of those big wigs to "VIP" or "Executive" or similar.  Then you can
write a much nicer query like this:

    Status = 'open' and Requestor.Organization = 'VIP'

This also means all you need to do when a big wig cashes out or a new
one comes in is to adjust their Organization, rather than potentially
edit a whole bunch of saved searches.

If you're already using Organization, you can use another user field
instead (some are searchable by default, others need a couple config
tweaks).

> Here's an example:
> 
> This TicketSQL:
> 
> Status = 'open' AND
> (
> Requestor.EmailAddress = 'bigwig0 at example.com' OR
> Requestor.EmailAddress = 'bigwig1 at example.com' OR
> Requestor.EmailAddress = 'bigwig2 at example.com' OR
> Requestor.EmailAddress = 'bigwig3 at example.com' OR
> Requestor.EmailAddress = 'bigwig4 at example.com' OR
> Requestor.EmailAddress = 'bigwig5 at example.com' OR
> Requestor.EmailAddress = 'bigwig6 at example.com' OR
> Requestor.EmailAddress = 'bigwig7 at example.com' OR
> Requestor.EmailAddress = 'bigwig8 at example.com' OR
> Requestor.EmailAddress = 'bigwig9 at example.com' OR
> Requestor.EmailAddress = 'bigwig10 at example.com' OR
> Requestor.EmailAddress = 'bigwig11 at example.com' OR
> Requestor.EmailAddress = 'bigwig12 at example.com' OR
> Requestor.EmailAddress = 'bigwig13 at example.com' OR
> Requestor.EmailAddress = 'bigwig14 at example.com' OR
> Requestor.EmailAddress = 'bigwig15 at example.com' OR
> Requestor.EmailAddress = 'bigwig16 at example.com' OR
> Requestor.EmailAddress = 'bigwig17 at example.com' OR
> Requestor.EmailAddress = 'bigwig18 at example.com' OR
> Requestor.EmailAddress = 'bigwig19 at example.com' OR
> Requestor.EmailAddress = 'bigwig20 at example.com' OR
> Requestor.EmailAddress = 'bigwig21 at example.com' OR
> Requestor.EmailAddress = 'bigwig22 at example.com' OR
> Requestor.EmailAddress = 'bigwig23 at example.com' OR
> Requestor.EmailAddress = 'bigwig24 at example.com' OR
> Requestor.EmailAddress = 'bigwig25 at example.com' OR
> Requestor.EmailAddress = 'bigwig26 at example.com' OR
> Requestor.EmailAddress = 'bigwig27 at example.com' OR
> Requestor.EmailAddress = 'bigwig28 at example.com'
> )