It would help even more to show the original "RT query" that generated this SQL.
Eric Schultz United Online > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf > Of Brian Kerr > Sent: Monday, July 24, 2006 7:00 AM > To: [email protected] > 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: [EMAIL PROTECTED] > > > 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 > _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] 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
