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

[Robert Spier]

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...

Off the top of my head... you could try this:

CREATE INDEX Transactions2 ON Transactions ( ObjectType, id );

but I don't think it'll help much (or at all)

What are your Tickets4 and Tickets5 indexes?  They're not in my schema
file.

We could also try creating something like

CREATE INDEX Tickets8 ON Tickets ( Type, EffectiveId, Status );

Maybe its time to look at the MySQL full text search capabilities
again.

-R