[rt-users] content search is a problem

[Roy El-Hames]

Hi;
the system is rt-3.6.1, mysql Distrib 5.1.12-beta, apach2 and mod_perl 
2,dbix 1.45
the hardware web server intel 3.2 G processor 4G memory and 1 db server 
athlon 2 dual core 2.2 G proc and 16 G memory
The system is optimised, and all functionality is fairly quick and 
smooth with the exception of content searches, a query like :
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 ((main.Created > '2006-07-14 23:00:00') AND (main.Queue != '132') 
AND (main.Status != 'rejected') AND (main.id > '178000') AND  ( 
(main.Subject LIKE '%my text%') OR  ( (Attachments_2.Content LIKE 
'%mytext%') AND (Attachments_2.TransactionId = Transactions_1.id) AND 
(main.id = Transactions_1.ObjectId) )  ) ) just hangs and not return 
anything and in the process effect the performance for other users an 
explain of the query :
-------------+
| id | select_type | table          | type  | 
possible_keys                                        | key           | 
key_len | ref   | rows    | Extra                    |
+----+-------------+----------------+-------+------------------------------------------------------+---------------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | main           | range | 
PRIMARY,Tickets1,Tickets3,Tickets4,Tickets5,Tickets6 | PRIMARY       | 
4       | NULL  |   83328 | Using where              |
|  1 | SIMPLE      | Transactions_1 | ref   | 
PRIMARY,Transactions1,Transactions2                  | Transactions1 | 
66      | const | 1894666 | Using where; Using index |
|  1 | SIMPLE      | Attachments_2  | ALL   | 
Attachments2                                         | NULL          | 
NULL    | NULL  | 7134357 | Using where              |

I am not sure what can be done to improve this, we have 230000 tickets 
and the attachments table have  1774299 rows and 5G in size, we 
partitioned the atatchments table into 100000 row segments , which 
improved everything else but I feel slows the content searching, has 
anyone got any ideas?? any help will truly be appreciated.
Regards;
Roy