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

[Joop van de Wege]

> Andrew Sullivan wrote:
> 
> > On Fri, Jun 24, 2005 at 02:12:56PM +0200, Rolf Grossmann wrote:
> > 
> >>that. IMHO it's a serious flaw in Oracle's query optimizer that adding a 
> >>condition that's always true significantly slows down a query.
> > 
> > Well, it still has to visit every row.  There's no way to know in
> > advance that the condition is _always_ true, because the optimiser is
> > cost-based (and therefore can only use statistics for this).  It's
> > possible that the planning step would take a different strategy
> > (using an index, say); but it'd still have to visit every row, and
> > using an index in that case would be even more expensive.
> 
> The point is, it doesn't need to use the condition on all records, only 
> those that match the other conditions. Also, for most searches (matching 
> text in some field like in the given query) the database needs to look 
> at every row anyway.
> 
> > The simple problem is that the condition isn't a selective one, and this
> > suggests to me that the query is a bad one, given the structure.  (My
> > experience overall with RT is that the automatic generation of SQL
> > causes all sorts of poorly-written queries like this, that generate
> > needless conditions that slow everything down).
> 
> Like Jesse and I said before, the condition is not needless.
> 
> Rolf
I started this thread before I had a talk with my DBA and since then I
got to understand better what is happening.
The query I showed was looking for a piece of text from  the body of an
email message which is stored in attachments.content. This column is
normally not indexed but I have added what is called a Context (domain) index
which Oracle uses to index CLOB/BLOB columns which can contain plain
text but also Word documents and almost anything Oracle has a filter for.
I also rewrite the query in Searchbuilder so that this index is used
whenever there is a search for CONTENT and 'contains' is used in RT
This query is only slow because it has no other restrictions besides the
ones added automatically like status!='deleted' and effectiveid=id.
If I understand correctly the last one is used to distinguish merged
tickets from normal ones? and this conditions is even worse in our
environment because we have about 20 merged tickets out of 15000 tickets
total.
Adding a queue to the search will bring down this endless search to
about 5 secs for the first execution and less then 1sec for subsequent
searches. We do understand why the query is build as it is and it is
impossible to make use of only the strong points of a database without
going to write queries specific for that database.
For those of you using Oracle:
We thought about using 'buckets' on some indices but because of our
search pattern this is probably not going to work. YMMV. ( what this
means is you could one of the six statuses assign a different weight in
which case the index might get used more often instead of a full table
scan)

Joop

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