RT 3.4.5, MySQL 4.1.18
If I perform a search for “Content like mysearchterm” the following
long-running queries are run on the DB:
Time: 061214 12:52:16
User@Host: rt_user[rt_user] @ localhost []
Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 546064
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType = ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ( ( (Attachments_2.Content LIKE
‘%mycontentsearch%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );
Time: 061214 12:52:28
User@Host: rt_user[rt_user] @ localhost []
Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 546064
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType = ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ( ( (Attachments_2.Content LIKE
‘%mycontentsearch%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );
Time: 061214 12:52:41
User@Host: rt_user[rt_user] @ localhost []
Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 546066
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType = ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ( ( (Attachments_2.Content LIKE
‘%mycontentsearch%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );
Time: 061214 12:52:53
User@Host: rt_user[rt_user] @ localhost []
Query_time: 12 Lock_time: 0 Rows_sent: 563 Rows_examined: 547192
SELECT DISTINCT main.* FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType = ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ( ( (Attachments_2.Content LIKE
‘%mycontentsearch%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ) ORDER BY
main.id ASC;
It looks like it is running the same expensive query 3 times in a row
for some reason. Is this nessecary?
Also, do you guys add indices to your attachments tables?
mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main ,
Transactions Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType = ‘RT::Ticket’)) AND ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’)) AND ( ( (Attachments_2.Content LIKE
‘%mycontentsearch%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) );
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows |
Extra |
| 1 | SIMPLE | Attachments_2 | ALL | Attachments2
| NULL | NULL | NULL | 409952 |
Using where |
| 1 | SIMPLE | Transactions_1 | eq_ref | PRIMARY,Transactions1
| PRIMARY | 4 | rt3.Attachments_2.TransactionId | 1 |
Using where |
| 1 | SIMPLE | main | eq_ref |
PRIMARY,Tickets4,Tickets5 | PRIMARY | 4 |
rt3.Transactions_1.ObjectId | 1 | Using where |
3 rows in set (0.00 sec)