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

[Schultz, Eric]

It would help even more to show the original "RT query" that generated
this SQL.

Eric Schultz
United Online 

> -----Original Message-----
> From: rt-users-bounces at lists.bestpractical.com 
> [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf 
> Of Brian Kerr
> Sent: Monday, July 24, 2006 7:00 AM
> To: rt-users at lists.bestpractical.com
> Subject: [rt-users] long running query that never finishes 
> (six days at 100%CPU)
> 
> 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,ObjectCustom
> FieldValues2
> | TicketCustomFieldValues1 |       8 |
> rt3.CustomFields_1.id,rt3.main.id |      1 | Distinct
>                    |
> |  1 | SIMPLE      | ObjectCustomFieldValues_4 | ref  |
> TicketCustomFieldValues1,TicketCustomFieldValues2,ObjectCustom
> FieldValues2
> | 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)
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
> 
> 
> We're hiring! Come hack Perl for Best Practical: 
> http://bestpractical.com/about/jobs.html
>