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?
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@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?
–
RT Training in Seattle, June 19-20: http://bestpractical.com/training
Best regards, Ruslan.
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’ )
I’m sure you’ve considered this, but I have to ask: Could you simplify
most of the CF conditions with CF.{utstyr} ENDSWITH ‘.uninett.no’?
For a technical solution, the approach Ruslan described is the way to
go. It works great for watchers.
Hi,
It’s not a bug, but missing feature.
Hm, ok. This problem makes searching for multiple values in CFs unusable
for our purposes. I would like to help in getting it fixed. For now I
have worked around it by searching for one CF value at a time in a
loop. Seems a bit silly since this is something SQL could handle easily.
Should I register this as a feature request somewhere, or is this
something that is already on the map?
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.
Yes, I had a quick look at the relevant git commits. I guess the problem
is similar.
I’m sure you’ve considered this, but I have to ask: Could you simplify
most of the CF conditions with CF.{utstyr} ENDSWITH ‘.uninett.no’?
Thanks for the suggestion, but no, that will not give me the result I am
after.
Hi,
It’s not a bug, but missing feature.
Hm, ok. This problem makes searching for multiple values in CFs unusable
for our purposes. I would like to help in getting it fixed. For now I
have worked around it by searching for one CF value at a time in a
loop. Seems a bit silly since this is something SQL could handle easily.
Should I register this as a feature request somewhere, or is this
something that is already on the map?
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.
Yes, I had a quick look at the relevant git commits. I guess the problem
is similar.
If you don’t have cycles and/or knoledge to help here then file a bug
report on http://issues.bestpractical.com .
Best regards, Ruslan.
Hi,
the Database indexes are not optimal for the ObjectCustomFieldValues
table. Sadly I didn’t had time to dig deeper into this.
But maybe you have time and an new index will help you.
There is already an ticket open for this:
http://issues.bestpractical.com/Ticket/Display.html?id=16898
Chris
Hi,
the Database indexes are not optimal for the ObjectCustomFieldValues
table. Sadly I didn’t had time to dig deeper into this.
But maybe you have time and an new index will help you.
Thanks for the tip. Better indexing might help, but in this case the SQL
query is clearly suboptimal, so a fix in the SQL generation is in order
anyway. In our database, a properly optimised SQL statement returns
results immediately.
There is already an ticket open for this:
Login
The symptoms might be the same (slow queries), so the tickets might be
related, but I think this is two separate issues which will require
different solutions. One issue is about suboptimal SQL, the other issue
is about suboptimal indexes.
Hi,
the Database indexes are not optimal for the ObjectCustomFieldValues
table. Sadly I didn’t had time to dig deeper into this.
But maybe you have time and an new index will help you.
Thanks for the tip. Better indexing might help, but in this case the SQL
query is clearly suboptimal, so a fix in the SQL generation is in order
anyway. In our database, a properly optimised SQL statement returns
results immediately.
In this case there is no better indexing. The SQL itself falls into
category of hardly optimizable by DBs. Some DBs do better job by using hash
joins, but mysql with its loop strategy can not cope.
There is already an ticket open for this:
Login
The symptoms might be the same (slow queries), so the tickets might be
related, but I think this is two separate issues which will require
different solutions. One issue is about suboptimal SQL, the other issue
is about suboptimal indexes.
–
RT Training in Seattle, June 19-20: http://bestpractical.com/training
Best regards, Ruslan.