[rt-users] slow full-content searches

[Brian Kerr]

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 at 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)