"and" search returns quickly, "or" search times out

[jkj2001]

I have a virtual field search I'm running, which looks like so:

tsql "select count(*) from mytable where THISFIELD\THATFIELD\AFIELD\NOFIELD like 'hello'"

It returns the count (around 12000) in a second or two.

I then run an almost identical search:

tsql "select count(*) from mytable where THISFIELD\THATFIELD\AFIELD\NOFIELD like 'goodbye'"

and again, I get a count returned very quickly.

When I try to OR the searches together, this takes many, many minutes to return:

tsql "select count(*) from mytable where THISFIELD\THATFIELD\AFIELD\NOFIELD like 'hello' OR THISFIELD\THATFIELD\AFIELD\NOFIELD like 'goodbye'"

However, and-ing the terms together returns a count much more quickly. I've replicated these searches several times over the past couple of hours, and the results are the same each time-- "and" search is quick, "or" search is very slow.

The virtual field has an inverted metamorph index on it, quite fresh. _T.btr is only 51 bytes.

I'm not up on advanced SQL theory, so maybe an OR search will take longer than AND, but at a factor like this? Sounds like we're not doing something right over here, and I was wondering if you could help point me in the right direction and see what might be going on.

We're using your Solaris version of texis, version 3.01.984591953. Thanks again for the help

[John]

With the version of Texis that you have the count can use the index to calculate the count without looking at the records. That is true for the regular and then AND query. The OR is reading the records to make sure they match due to complexities in what it can know is a match.

[mark]

This would be faster
THISFIELD\THATFIELD\AFIELD\NOFIELD like 'hello goodbye @0'"