[Rt-devel] Question about Type column in table Tickets

[Joop van de Wege]

Hello All,

I have been looking at the performance of our RT-3.4.2 which uses
Oracle9i. I got some complaints that searching took so long and had a
look myself and found a problem which at least at our site kills
performance for searching on content.
This is a search for problems on a machine called TZP065.
This construct I made myself and is how Oracle most efficiently searches
CLOB columns and has always worked like a charm.
   AND ((    (contains (attachments_2.content, 'tzp065') > 0)

/* Formatted on 2005/06/24 09:11 (Formatter Plus v4.8.5) */
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 ((    (contains (attachments_2.content, 'tzp065') > 0)
         AND (attachments_2.transactionid = transactions_1.ID)
         AND (main.ID = transactions_1.objectid)
        )
       )

The problem is in this line:
   AND ((main.TYPE = 'ticket'))
I made several extra indices among them one on Ticket.Type. This turns
out to be not neccesary since there is only one Type, 'ticket' and thus
causes a full table scan against all Tickets ;-((

Eliminating that line gives me reasonable query times, around 5sec.

Now the question:
Are there gonna be other types in the ticket table?
If NO then this line can be removed, if YES can it be disabled until the
time that there are other types in the ticket table?

Joop

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