compound metamorph index problem

kevin31 · Post by **kevin31** » Fri May 02, 2003 4:13 pm

I have a table that looks like the following
<SQL "CREATE TABLE tForms (FormID int, FormType int, TextBlob varchar(256))"></SQL>

and I create an index on it like so:
<SQL "CREATE METAMORPH INVERTED INDEX IXT_tForms on tForms (TextBlob, FormID, FormType)"></SQL>

When I do a likep search on TextBlob a linear search happens as evidenced by the fact that hits occur on substrings, i.e. a search on "bar" will hit on "foobarquack". This will be too slow for large data sets. Is there something wrong with the creation of my index? "allinear" has been left at its default value of 0.

My texis version is
Commercial Version 3.01.992447526 of Jun 13, 2001 (i686-intel-winnt-32)

Post by **Kai** » Fri May 02, 2003 4:44 pm

What is the exact query you're doing (both SQL and $-parameters)?

kevin31 · Post by **kevin31** » Fri May 02, 2003 5:16 pm

The query I am doing was from the TSQL command prompt:
SQL 1>select * from tForms where TextBlob likep 'bar';

I made no modifications to apicp settings before doing this query, so I assume everything is set to default values.
Regarding index updating, this query was done following the new creation of the metamorph index.

kevin31 · Post by **kevin31** » Fri May 02, 2003 6:16 pm

Is it possible that records found after the creation of the index might be linearly searched even though the rest of the table is not? My concern was that the entire table was being linearly searched which would be extremely slow in a live situation.

Post by **mark** » Fri May 02, 2003 8:58 pm

By default rows added after the latest index update will be searched linearly (as mentioned in msgs 3 and 5).

In any event "...likep 'bar'" should only return records that have the word.