Context Optimization

From: <bernz_at_bernztech.org>
Date: Wed, 22 Dec 1999 19:24:35 GMT
Message-ID: <83r8hd$2kd$1@nnrp1.deja.com>

I'm using context, which i'm fairly new at. I have multiple contains() statements in a single query. If i use AND to separate them, the query takes mere miliseconds (which is what i really want) but if i separate with or, it takes about 12 seconds, which strikes me as odd. without the contains() statements, it takes mere  miliseconds as well.
Can anyone help me with optimizing it as EXPLAIN PLAN doesn't recognize  contains()?
this query WORKS it just seems to take an abnormal ammount of time to work.
the query is as follows...

select

tab_forum.for_name,
tab_question.for_id,
tab_question.qtn_id,
tab_question.cus_id,
tab_question.qtn_subject,
tab_answer.ans_value,
tab_answer.exp_id,
tab_answer.ans_id,

round(avg(tab_billing.bill_expertscore),2) as expscore, count(tab_billing.ans_id) as count
from

tab_forum,
tab_question,
tab_answer,
tab_billing

where
tab_forum.for_id = tab_question.for_id
and (contains(tab_question.qtn_text,'%test%',10) >0 or contains(tab_answer.ans_text,'%test%',20) > 0)

and tab_question.qtn_id = tab_answer.qtn_id
and tab_answer.ans_id = tab_billing.ans_id
and tab_question.qtn_private = 2
and tab_billing.bill_expertscore >= 1
and tab_billing.bill_expertscore <= 10
and tab_forum.for_id in

(select for_id from tab_permission where grp_id in (select grp_id from tab_groupuser where tab_groupuser.usr_id = 'guest' and tab_groupuser.gus_who = 2))
group by tab_forum.for_name,

tab_question.for_id,
tab_question.qtn_id,
tab_question.cus_id,
tab_question.qtn_subject,
tab_answer.ans_value,
tab_answer.exp_id,
tab_answer.ans_id

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 22 1999 - 13:24:35 CST