RT 3.4.5, 4.1.18, ~80,000 tickets
Searching across ~30 queues with ~80,000 tickets total:
Fastcgi timeout is set to 120 seconds, which this query and many other
full-text queries surpass.
Time: 061026 14:09:35
User@Host: rt_user[rt_user] @ localhost []
Query_time: 156 Lock_time: 0 Rows_sent: 6 Rows_examined: 1285438
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 ‘%different
results%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ) ORDER BY
main.id ASC;
Explain:
mysql> explain 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 ‘%different
results%’)AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ) ORDER BY
main.id ASC;
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
| 1 | SIMPLE | main | ALL | PRIMARY,Tickets4,Tickets5
| NULL | NULL | NULL | 76267 | Using
where; Using temporary; Using filesort |
| 1 | SIMPLE | Transactions_1 | ref | PRIMARY,Transactions1
| Transactions1 | 68 | const,rt3.main.id | 3 | Using
where; Using index; Distinct |
| 1 | SIMPLE | Attachments_2 | ref | Attachments2
| Attachments2 | 4 | rt3.Transactions_1.id | 1 | Using
where; Distinct |
3 rows in set (0.00 sec)