index behavior

[kevin12]

When we failed-over to a new machine one of our clients' searches began timing out, and we're trying to understand the behavior.

This search times out:
select count(*) from article where body not like 'hsbc' and content_id in ('ap.international','ap.top_news');

But reversing the clauses makes it work fine:
select count(*) from article where content_id in ('ap.international','ap.top_news') and body not like 'hsbc';

There is a metamorph inverted index bcc_vidx on (body, content_id)
and a regular index on content_id.

tsql -V shows the first (bad, timeout) search reading from the metamorph index:
200 Looking for index on article (body)
200 Opening index /p/data/texis/fulltext/bcc_vidx in the function ixfmmindex
and then apparently scanning the entire table(?)

and the second (good) search reading the content_id index first, and then the metamorph index.
200 Looking for index on article (content_id)
200 Opening index /p/data/texis/fulltext/content_id_idx in the function ixbteqindex
...
200 Comparing records
200 Looking for index on article (body)
200 Opening index /p/data/texis/fulltext/bcc_vidx in the function ixfmmindex
9

The search that is timing out now was working fine on the old machine which had an identical setup, though we can't examine the machine as the hardware is still kaput.

We can get good behavior by switching the clauses, but I'd like to have a better understanding of what's going on.

Can you help shed any light on this?

[John]

We'll need to check into that. NOT LIKE shouldn't be using the index in either case, so it should just rely on the regular index oncontent_id. Was it the same version of Texis on both machines?

[kevin12]

Yes, the version on the same:
Commercial Version 3.01.976899697 of Dec 15, 2000 (sparc-sun-solaris2.6)

NOT LIKE doesn't use an index?

[John]

No, in general a NOT query won't use an index, as it would expect to be left with a lot of possible records to look at, and not be an improvement over a linear search.