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