Thunderstone Support Forums

Hi,

We have a user who wants to search our database for a word or term that ends in "nx"-- and there are other ending characters they want to search on too. Is it possible to create an index that would work well here?

A typical search would be something like: "select * from mytable where (mytable.FIELD1\FIELD2\FIELD3 like '*nx')"

A Metamorph index can be used to resolve this, in Texis versions after 4.03.1058210000 (July 2003). Set wildsingle=1:

<sql "set wildsingle=1"></sql>

which changes wildcard behavior (for the index only) such that wildcards are presumed to span only one word (not 80 chars), and suffix-match words only.

This will allow a linear scan of the dictionary to be used, which is not as fast as a regular binary lookup, but is still much faster than a no-index linear table scan.

Thanks, Kai.

I already have an metamorph index on this field. Are you saying I need to build a new one, or can the wildsingle flag use the one I have, by way of a command like this?

tsql "set wildsingle=1;select * from mytable where (mytable.FIELD1\FIELD2\FIELD3 like '*nx')"

We're on version 4.04.1067366033, dated October 2003, so wildsingle is available to us.

Ah....one more thing, if I could. When I run my search just now I get a message saying "'prefixes less than 1 characters (*nx)' not allowed in query". I forget; which flag(s) will force tsql to process the query?

You do not need to build a new index, just set wildsingle=1 before doing the LIKE/LIKEP search, as per your example.

For the prefix message, you'll also need to set <apicp qminprelen 0> to allow a 0-length wildcard prefix.