Thunderstone Support Forums

Hi,
We had some descrepancies, when searching for punctuations and we figured the following out,

1. The tests we were doing on small record set(maybe 20rows or so) were using the index.
2. But, the larger production table with 1/2 mill to million records was not using indexes at all, so doing linear search, and was hence doing sustring matches.

the searches are of the kind,
FIELD1 like 'john&' or FIELD1 like 'john@'

Our index expressions index punctuations by itself but not as part of other words.

Would the sustring match be solved by adding the punctuations to langc/wordc?

thanks in advance for the help!!

Searching for any character not in langc will allow it to do substring matching.

and, oh, any reason why index is used sometimes and not in others.
anyway to make them use index all the time
?

Are there any messages? If you have other WHERE clauses that already restrict the result set substantially it will avoid using an index on later clauses as looking at the records will be less work.

See also maxlinearrows

we did not get any other messages.
The only clause is

DOCTEXT like 'an&'

Here is our index expression,
"set keepnoise='on';set delexp=0;set addexp='\punct{1,5}';set addexp='\alnum{1,99}';set addexp='>>\alpha{1,50},=\alpha{1,50}';create metamorph inverted index idxdoctext on hyphentest(DOCTEXT);"

As you see, punctuations are indexed by itself.

Scenario 1. Table with 20 records and the above index,

search for DOCTEXT like 'an&'
get this result.

DOCID DOCTEXT
------------+------------+
201 difference between an& is the following
206 gummy 1 DY JAN AN& the office

Scenario 2.

same index as above, but atleast 1/2 mill records, same query, gives the following output.

DOCTEXT
---------
gummy 1 DYJAN&0 17his does not, however, replace our..

will maxlinearrows explain this?
thanks!!

Is the index up to date on the larger table? If not it will go linear on the records added since the index was updated.