Tim

Why if I have the OR'd TEXTSEARCH does this query take ages (128 secs but with only one of the TEXTSEARCH's its fast (.11sec for altbert and 1.27sec for presentations)?

Running V4.22b5

SELECT _MsgNo, _BoxNo, _fkCompanies, _fkContacts, _InOutInd,IF(_Attachments IS NOT NULL THEN TRUE ELSE FALSE) AS _xAttachmentsExist,IF(_Additional IS NULL THEN _Subject ELSE _Subject + '[' +_Additional + ']') AS _xDisplay,_TimeStamp, _Consultant,_Format,IF(_WhoFrom IS NOT NULL THEN _WhoFrom ELSE _WhoTo) As _xWho INTO Memory\Search2231158
FROM MandN T0
WHERE
TEXTSEARCH ('albert' IN T0._Message) OR
TEXTSEARCH ('presentations' IN T0._Message)

AND
(T0._BoxNo >= -11
OR
CAST(T0._BoxNo / -100000 AS INTEGER) = 4)

Roy Lambert

Are you sure you got the logic right? I'd put both ORs inside parenthesis,
otherwise it may be that the second one is evaluated with AND first

A or B and C => A or (B and C)

In this case A condition will not be sufficient to prevent full table scan
if C cannot be optimized by indexes (IMO, your CAST prevents this).
Try executing without C and try with parenthesis as above.

Michael


Roy Lambert wrote:
> Tim
>
> Why if I have the OR'd TEXTSEARCH does this query take ages (128 secs but
> with
only one of the TEXTSEARCH's its fast (.11sec for altbert and 1.27sec for
presentations)?
>
> Running V4.22b5
>
> SELECT _MsgNo, _BoxNo, _fkCompanies, _fkContacts, _InOutInd,IF(_Attachments IS NOT NULL THEN TRUE ELSE FALSE) AS _xAttachmentsExist,IF(_Additional IS NULL THEN _Subject ELSE _Subject + '[' +_Additional + ']') AS _xDisplay,_TimeStamp, _Consultant,_Format,IF(_WhoFrom IS NOT NULL THEN _WhoFrom ELSE _WhoTo) As _xWho INTO Memory\Search2231158
> FROM MandN T0
> WHERE
> TEXTSEARCH ('albert' IN T0._Message) OR
> TEXTSEARCH ('presentations' IN T0._Message)
>
> AND
> (T0._BoxNo >= -11
> OR
> CAST(T0._BoxNo / -100000 AS INTEGER) = 4)
>
> Roy Lambert

> .... and try with parenthesis as above.
What I meant is (A or B) and C - is not what I said .


Michael


>
> Michael
>
>
> Roy Lambert wrote:
>> Tim
>>
>> Why if I have the OR'd TEXTSEARCH does this query take ages (128 secs but
>> with
> only one of the TEXTSEARCH's its fast (.11sec for altbert and 1.27sec for
> presentations)?
>>
>> Running V4.22b5
>>
>> SELECT _MsgNo, _BoxNo, _fkCompanies, _fkContacts,
>> _InOutInd,IF(_Attachments IS NOT NULL THEN TRUE ELSE FALSE) AS
>> _xAttachmentsExist,IF(_Additional IS NULL THEN _Subject ELSE _Subject
>> + '[' +_Additional + ']') AS _xDisplay,_TimeStamp,
>> _Consultant,_Format,IF(_WhoFrom IS NOT NULL THEN _WhoFrom ELSE _WhoTo)
>> As _xWho INTO Memory\Search2231158
>> FROM MandN T0
>> WHERE
>> TEXTSEARCH ('albert' IN T0._Message) OR
>> TEXTSEARCH ('presentations' IN T0._Message)
>>
>> AND
>> (T0._BoxNo >= -11
>> OR
>> CAST(T0._BoxNo / -100000 AS INTEGER) = 4)
>>
>> Roy Lambert

Michael


Dead right - stupid sql parser - you'd think it would know what I meant

Thanks


Roy Lambert

Roy,

<< Dead right - stupid sql parser - you'd think it would know what I meant
>>

I told you guys it was dumb compared to a human, but you didn't believe me.


--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Which human are you comparing it to - I know some who would get it even more wrong (me for instance)!


Roy Lambert

+1.

Roy Lambert wrote:
> Tim
>
>
> Which human are you comparing it to - I know some who would get it even more wrong (me for instance)!
>
>
> Roy Lambert
>

Michael


Sometimes I have difficulty following your posts because they are to technical for my simple brain. This one for instance .....

Roy Lambert

Roy Lambert wrote:
> Michael
>
>
> Sometimes I have difficulty following your posts because they are to
> technical for my simple brain. This one for instance .....

I think he means "Inc(human_who_would_get_it_even_more_wrong)". Which I want
to execute, too.

Ralf

Ralf


Got it - thanks

Roy Lambert