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 < [email protected]> 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.
-- RT Training in Seattle, June 19-20: http://bestpractical.com/training
