[rt-users] Query taking 30 minutes and more

[Joop]

Marlier, Ian wrote:
>  
> 
>> -----Original Message-----
>> From: rt-users-bounces at lists.bestpractical.com 
>> [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf 
>> Of Uli Staerk
>> Sent: Monday, August 13, 2007 6:38 AM
>> To: rt-users at lists.bestpractical.com
>> Subject: [rt-users] Query taking 30 minutes and more
>>
>> When I run the following query:
>> Content LIKE 'schäfer'
>> OR Subject LIKE 'schäfer'
>> OR Subject LIKE 'tekom'
>> OR Content LIKE 'tekom'
>> OR Requestor.EmailAddress LIKE 'tekom'
>> OR Requestor.EmailAddress LIKE 'schaefer'
>> OR Subject LIKE 'schaefer'
>> OR Content LIKE 'schaefer'
>> OR Requestor.Name LIKE 'schaefer'
>> OR Requestor.Name LIKE 'schäfer'
>>
>> This Query takes (currently) 30 Minutes and I'm still 
>> waiting. Can anyone reproduce this problem? I'm using RT 3.4.5
> 
> I had occasional problems like that with 3.4.5, and never did find a solution.  Upgrading to RT 3.6 cleared them up.  It might be worth looking into....
The OR Requestor part is speeded up in RT 3.6.4 but there is a second 
problem which is the searching for %conten% in the Content column. I 
know it is standard policy to pre and post pend % on search terms but 
this way it will always kill performence when you search for ticket body 
text.
One way to solve this is to stop the pre/post pend action and explicitly 
ask for it in the query builder.
Second, Mysql doesn't index Content so this always leads to a full 
tablescan which can hurt really bad if you have lots of large attachments.
The only solution that I know of is to switch to Oracle and use a couple 
of patches so that you enable OracleText on that column.

Joop