[rt-users] Slow query on CFs

[Ruslan Zakirov]

Hi,

It's not a bug, but missing feature. It's possible to improve using similar
improvements to searches by watchers. If you want to help then you can take
a look at recent changes in lib/RT/Tickets_SQL.pm.


On Tue, Jun 4, 2013 at 5:03 PM, Vegard Vesterheim <
vegard.vesterheim at uninett.no> wrote:

> A RT (TicketSQL) query like this:
>
> Queue = 'drift' AND (  Status = 'new' or Status = 'open' or Status =
> 'stalled' ) AND (  CF.{utstyr} = 'ufisaweb.uninett.no' OR CF.{utstyr} =
> 'dss2.uninett.no' OR CF.{utstyr} = 'myrhauk.uninett.no' OR CF.{utstyr} =
> 'angel.uninett.no' OR CF.{utstyr} = 'jatoba-esxi2.uninett.no' OR
> CF.{utstyr} = 'bold.uninett.no' OR CF.{utstyr} = 'nidar.uninett.no' OR
> CF.{utstyr} = 'voll.uninett.no' OR CF.{utstyr} = 'brekka.uninett.no' OR
> CF.{utstyr} = 'www.stroemme.no' OR CF.{utstyr} = 'xen.uninett.no' OR
> CF.{utstyr} = 'jatoba-kvm4.uninett.no' OR CF.{utstyr} =
> 'inventory.uninett.no' OR CF.{utstyr} = 'busy.uninett.no' OR CF.{utstyr}
> = 'ufisa.uninett.no' OR CF.{utstyr} = 'wildfire.uninett.no' OR
> CF.{utstyr} = 'newfire.uninett.no' OR CF.{utstyr} = 'fou1.uninett.no' OR
> CF.{utstyr} = 'kanari.uninett.no' OR CF.{utstyr} = 'dok.uninett.no' OR
> CF.{utstyr} = 'ebony-kvm5.uninett.no' OR CF.{utstyr} =
> 'jatoba-kvm12.uninett.no' )
>
> gets translated to the following SQL:
>
> SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_5 ON (
> ObjectCustomFieldValues_5.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_5.Disabled = '0' ) AND (
> ObjectCustomFieldValues_5.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_5.CustomField = '8' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_3 ON (
> ObjectCustomFieldValues_3.CustomField = '8' ) AND (
> ObjectCustomFieldValues_3.Disabled = '0' ) AND (
> ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_3.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_22 ON (
> ObjectCustomFieldValues_22.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_22.CustomField = '8' ) AND (
> ObjectCustomFieldValues_22.Disabled = '0' ) AND (
> ObjectCustomFieldValues_22.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_19 ON (
> ObjectCustomFieldValues_19.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_19.CustomField = '8' ) AND (
> ObjectCustomFieldValues_19.Disabled = '0' ) AND (
> ObjectCustomFieldValues_19.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_10 ON (
> ObjectCustomFieldValues_10.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_10.CustomField = '8' ) AND (
> ObjectCustomFieldValues_10.Disabled = '0' ) AND (
> ObjectCustomFieldValues_10.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_4 ON (
> ObjectCustomFieldValues_4.Disabled = '0' ) AND (
> ObjectCustomFieldValues_4.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_4.CustomField = '8' ) AND (
> ObjectCustomFieldValues_4.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_8 ON (
> ObjectCustomFieldValues_8.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_8.CustomField = '8' ) AND (
> ObjectCustomFieldValues_8.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_8.Disabled = '0' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_21 ON (
> ObjectCustomFieldValues_21.CustomField = '8' ) AND (
> ObjectCustomFieldValues_21.Disabled = '0' ) AND (
> ObjectCustomFieldValues_21.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_21.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_12 ON (
> ObjectCustomFieldValues_12.CustomField = '8' ) AND (
> ObjectCustomFieldValues_12.Disabled = '0' ) AND (
> ObjectCustomFieldValues_12.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_12.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_16 ON (
> ObjectCustomFieldValues_16.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_16.Disabled = '0' ) AND (
> ObjectCustomFieldValues_16.CustomField = '8' ) AND (
> ObjectCustomFieldValues_16.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_17 ON (
> ObjectCustomFieldValues_17.Disabled = '0' ) AND (
> ObjectCustomFieldValues_17.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_17.CustomField = '8' ) AND (
> ObjectCustomFieldValues_17.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_20 ON (
> ObjectCustomFieldValues_20.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_20.CustomField = '8' ) AND (
> ObjectCustomFieldValues_20.Disabled = '0' ) AND (
> ObjectCustomFieldValues_20.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_13 ON (
> ObjectCustomFieldValues_13.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_13.Disabled = '0' ) AND (
> ObjectCustomFieldValues_13.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_13.CustomField = '8' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_2 ON (
> ObjectCustomFieldValues_2.Disabled = '0' ) AND (
> ObjectCustomFieldValues_2.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_2.CustomField = '8' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_9 ON (
> ObjectCustomFieldValues_9.CustomField = '8' ) AND (
> ObjectCustomFieldValues_9.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_9.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_9.Disabled = '0' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_18 ON (
> ObjectCustomFieldValues_18.Disabled = '0' ) AND (
> ObjectCustomFieldValues_18.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_18.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_18.CustomField = '8' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_14 ON (
> ObjectCustomFieldValues_14.CustomField = '8' ) AND (
> ObjectCustomFieldValues_14.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_14.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_14.Disabled = '0' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_7 ON (
> ObjectCustomFieldValues_7.Disabled = '0' ) AND (
> ObjectCustomFieldValues_7.CustomField = '8' ) AND (
> ObjectCustomFieldValues_7.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_7.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_6 ON (
> ObjectCustomFieldValues_6.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_6.CustomField = '8' ) AND (
> ObjectCustomFieldValues_6.Disabled = '0' ) AND (
> ObjectCustomFieldValues_6.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_1 ON (
> ObjectCustomFieldValues_1.CustomField = '8' ) AND (
> ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_1.Disabled = '0' ) AND (
> ObjectCustomFieldValues_1.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_11 ON (
> ObjectCustomFieldValues_11.Disabled = '0' ) AND (
> ObjectCustomFieldValues_11.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_11.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_11.CustomField = '8' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_15 ON (
> ObjectCustomFieldValues_15.Disabled = '0' ) AND (
> ObjectCustomFieldValues_15.CustomField = '8' ) AND (
> ObjectCustomFieldValues_15.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_15.ObjectId = main.id ) WHERE (main.Status !=
> 'deleted') AND (main.Queue = '6' AND ( main.Status = 'new' or
> main.Status = 'open' or main.Status = 'stalled' ) AND ( ( ( (
> LOWER(ObjectCustomFieldValues_1.Content) = 'ufisaweb.uninett.no' ) ) )
> OR ( ( ( LOWER(ObjectCustomFieldValues_2.Content) = 'dss2.uninett.no' )
> ) ) OR ( ( ( LOWER(ObjectCustomFieldValues_3.Content) =
> 'myrhauk.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues_4.Content) = 'angel.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues_5.Content) = 'jatoba-esxi2.uninett.no'
> ) ) ) OR ( ( ( LOWER(ObjectCustomFieldValues_6.Content) =
> 'bold.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues_7.Content) = 'nidar.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues_8.Content) = 'voll.uninett.no' ) ) )
> OR ( ( ( LOWER(ObjectCustomFieldValues_9.Content) = 'brekka.uninett.no'
> ) ) ) OR ( ( ( LOWER(ObjectCustomFieldValues_10.Content) =
> 'www.stroemme.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues_11.Content) = 'xen.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues_12.Content) = 'jatoba-kvm4.uninett.no'
> ) ) ) OR ( ( ( LOWER(ObjectCustomFieldValues_13.Content) =
> 'inventory.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues_14.Content) = 'busy.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues_15.Content) = 'ufisa.uninett.no' ) ) )
> OR ( ( ( LOWER(ObjectCustomFieldValues_16.Content) =
> 'wildfire.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues_17.Content) = 'newfire.uninett.no' ) ) )
> OR ( ( ( LOWER(ObjectCustomFieldValues_18.Content) = 'fou1.uninett.no' )
> ) ) OR ( ( ( LOWER(ObjectCustomFieldValues_19.Content) =
> 'kanari.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues_20.Content) = 'dok.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues_21.Content) = 'ebony-kvm5.uninett.no'
> ) ) ) OR ( ( ( LOWER(ObjectCustomFieldValues_22.Content) =
> 'jatoba-kvm12.uninett.no' ) ) ) ) ) AND (main.Type = 'ticket') AND
> (main.EffectiveId = main.id)
>
> This query takes a very long time to finish.
>
> A more sensible SQL query would be something like:
>
> SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN
> ObjectCustomFieldValues ON (
> ObjectCustomFieldValues.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues.Disabled = '0' ) AND (
> ObjectCustomFieldValues.ObjectId = main.id ) AND (
> ObjectCustomFieldValues.CustomField = '8' ) WHERE (main.Status !=
> 'deleted') AND (main.Queue = '6' AND ( main.Status = 'new' or
> main.Status = 'open' or main.Status = 'stalled' ) AND ( ( ( (
> LOWER(ObjectCustomFieldValues.Content) = 'ufisaweb.uninett.no' ) ) )
> OR ( ( ( LOWER(ObjectCustomFieldValues.Content) = 'dss2.uninett.no' )
> ) ) OR ( ( ( LOWER(ObjectCustomFieldValues.Content) =
> 'myrhauk.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues.Content) = 'angel.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues.Content) = 'jatoba-esxi2.uninett.no'
> ) ) ) OR ( ( ( LOWER(ObjectCustomFieldValues.Content) =
> 'bold.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues.Content) = 'nidar.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues.Content) = 'voll.uninett.no' ) ) )
> OR ( ( ( LOWER(ObjectCustomFieldValues.Content) = 'brekka.uninett.no'
> ) ) ) OR ( ( ( LOWER(ObjectCustomFieldValues.Content) =
> 'www.stroemme.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues.Content) = 'xen.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues.Content) = 'jatoba-kvm4.uninett.no'
> ) ) ) OR ( ( ( LOWER(ObjectCustomFieldValues.Content) =
> 'inventory.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues.Content) = 'busy.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues.Content) = 'ufisa.uninett.no' ) ) )
> OR ( ( ( LOWER(ObjectCustomFieldValues.Content) =
> 'wildfire.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues.Content) = 'newfire.uninett.no' ) ) )
> OR ( ( ( LOWER(ObjectCustomFieldValues.Content) = 'fou1.uninett.no' )
> ) ) OR ( ( ( LOWER(ObjectCustomFieldValues.Content) =
> 'kanari.uninett.no' ) ) ) OR ( ( (
> LOWER(ObjectCustomFieldValues.Content) = 'www3.uninett.no' ) ) ) OR (
> ( ( LOWER(ObjectCustomFieldValues.Content) = 'ebony-kvm5.uninett.no'
> ) ) ) OR ( ( ( LOWER(ObjectCustomFieldValues.Content) =
> 'jatoba-kvm12.uninett.no' ) ) ) ) ) AND (main.Type = 'ticket') AND
> (main.EffectiveId = main.id);
>
> This query is much more effective. Is this a bug?
>
>  - Vegard V -
>
>
>
> --
> RT Training in Seattle, June 19-20: http://bestpractical.com/training
>



-- 
Best regards, Ruslan.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20130604/27516edc/attachment.htm>