[rt-users] Slow query on CFs

[Vegard Vesterheim]

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 -