[Rt-devel] Re: slowness around 3.4.1 and custom fields?

[Joop van de Wege]

On Fri, 25 Mar 2005 01:58:13 -0500 (EST)
> 
> 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 '%customer%')
>           AND(Attachments_2.TransactionId = Transactions_1.id)
>           AND(main.id = Transactions_1.ObjectId)
>          )
>        OR(main.Subject LIKE '%customer%'));
> 
> > the slow logs say it took 30 minutes to run.
> 
> Eeep.
> 
> This one is harder to fix, and I don't have the right shaped database
> to test on.  The LIKEs on Content and Subject are *very* expensive...
> 
> CREATE INDEX Transactions2 ON Transactions ( ObjectType, id );
> but I don't think it'll help much (or at all)
It won't because of the '%' in front of the part you want to search on.
It will your index search and most of not all databases will revert to
full table scans hence the 30min runtime.
Oracle has the same problem and I cheated on two fronts to get out of
this kind of problems:
- I use the full text search capabilities of Oracle
- hacked the searchbuilder part where the '%' are pre and post pended to
the query string.

I told my people to use explicit  '%' when they want to search with
wildcards AND told them that if they search on Content AND use a
wildcard in front of their search that they are gone wait a bit and that
their collegues would get no more coffee for them ;-)

So the way to go for mysql is to use full text indices.

If anyone is interested in what I have done for Oracle I could make a
patch, which is not general, I don't know how to check if the dbh is an
Oracle connection or not. If this is possible then the patch should work
for anybody whether you're using Oracle or not.

Joop

-- 
Joop van de Wege <JoopvandeWege at mococo.nl>