[rt-users] more full context search mysql problems

[Brian Kerr]

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