[rt-users] long running query that never finishes (six days at 100% CPU)

[Brian Kerr]

Hi,

We are seeing some strange behavior on certain queries.  For whatever
reason they never finish.  I have attached an example query and the
explain query.

We are running RT 3.4.5 + mysql 4.1.18-log which was migrated from RT3.0.7

Thanks,
Brian

This might be easier to read / formatted properly at
http://rafb.net/paste/results/MtBiv132.html

|    3 | rt_user | localhost | rt3  | Query   | 533666 | Copying to
tmp table |SELECT DISTINCT main.* FROM ((((Tickets main  LEFT JOIN
CustomFields CustomFields_1  ON ( CustomFields_1.Name = 'Actionable'))
 LEFT JOIN CustomFields CustomFields_3  ON ( CustomFields_3.Name =
'IR'))  LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2
ON ( ObjectCustomFieldValues_2.CustomField = CustomFields_1.id) AND (
(ObjectCustomFieldValues_2.Disabled = '0')) AND (
(ObjectCustomFieldValues_2.ObjectId = main.id)) AND (
(ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket')))  LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_4  ON
((ObjectCustomFieldValues_4.ObjectId = main.id)) AND (
(ObjectCustomFieldValues_4.Disabled = '0')) AND (
(ObjectCustomFieldValues_4.ObjectType = 'RT::Ticket')) AND (
ObjectCustomFieldValues_4.CustomField = CustomFields_3.id)) ,
Transactions Transactions_5, Attachments Attachments_6  WHERE
((Transactions_5.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId =
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type =
'ticket')) AND ((main.Priority = '75')OR(main.Created > '2006-01-01
05:00')OR(main.Queue = '12')OR(ObjectCustomFieldValues_2.Content LIKE
'%Yes%')OR(ObjectCustomFieldValues_4.Content LIKE '%Yes%')OR (
(Attachments_6.Content LIKE '%Incident
Report%')AND(Attachments_6.TransactionId =
Transactions_5.id)AND(main.id = Transactions_5.ObjectId) ) )  ORDER BY
main.id ASC |


+----+-------------+---------------------------+------+----------------------------------------------------------------------------+--------------------------+---------+-----------------------------------+--------+----------------------------------------------+
| id | select_type | table                     | type | possible_keys
                                                            | key
                | key_len | ref                               | rows
| Extra                                        |
+----+-------------+---------------------------+------+----------------------------------------------------------------------------+--------------------------+---------+-----------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | main                      | ALL  |
PRIMARY,Tickets1,Tickets4,Tickets5
    | NULL                     |    NULL | NULL
      |  65814 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | CustomFields_1            | ALL  | NULL
                                                            | NULL
                |    NULL | NULL                              |     78
| Distinct                                     |
|  1 | SIMPLE      | CustomFields_3            | ALL  | NULL
                                                            | NULL
                |    NULL | NULL                              |     78
| Distinct                                     |
|  1 | SIMPLE      | ObjectCustomFieldValues_2 | ref  |
TicketCustomFieldValues1,TicketCustomFieldValues2,ObjectCustomFieldValues2
| TicketCustomFieldValues1 |       8 |
rt3.CustomFields_1.id,rt3.main.id |      1 | Distinct
                   |
|  1 | SIMPLE      | ObjectCustomFieldValues_4 | ref  |
TicketCustomFieldValues1,TicketCustomFieldValues2,ObjectCustomFieldValues2
| TicketCustomFieldValues1 |       8 |
rt3.CustomFields_3.id,rt3.main.id |      1 | Distinct
                   |
|  1 | SIMPLE      | Transactions_5            | ref  |
PRIMARY,Transactions1
    | Transactions1            |      64 | const
      | 287026 | Using where; Using index; Distinct           |
|  1 | SIMPLE      | Attachments_6             | ALL  | Attachments2
                                                            | NULL
                |    NULL | NULL                              | 258765
| Using where; Distinct                        |
+----+-------------+---------------------------+------+----------------------------------------------------------------------------+--------------------------+---------+-----------------------------------+--------+----------------------------------------------+
7 rows in set (0.00 sec)