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.
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?
