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)