Long Running Query

[source1ben]

I am trying to perform the following query against a table in our database that has 8.7 million rows. It seems to run forever (never comes back). I can run this same query against a smaller version of the same table (100,000 rows) and it runs fine. FYI - column2 is a blob data type.

select column1, column2, column3 from table where (column1\column2 like '"pf chang"' or column1\column2 like 'p.f.chang' or column1\column2 like '"p.f. chang"' or column1\column2 like '"pf changs"' or column1\column2 like 'p.f.changs' or column1\column2 like '"p.f. changs"' or column1\column2 like '"pf chang''s"' or column1\column2 like 'p.f.chang''s' or column1\column2 like '"p.f. chang''s"') and columndate > '-30 days'

Is there anything I can do to speed up the performance of complex queries like the one in my example?

thx.

[Kai]

You're running 10 separate queries there. For starters, when ORing multiple terms for a LIKE against the same column, combine them into one Metamorph query:

... colA like 'alpha' or colA like 'beta' or colA like 'gamma' ...

becomes:

... colA like 'alpha beta gamma @0' ...

See the Texis manual for more on Metamorph query syntax: the @0 means "0 intersections" or only one set is required.

You can also combine some query terms using wildcards, eg. 'p.f. chang*' covers 'p.f. chang', 'p.f. chang''s', etc.

Also, unless you've changed your index expression, those periods in the query terms will require post-processing to resolve, slowing the query further. You may want to drop the metamorph index and remake it with different index expressions. (Also add columndate as a compound field, if it truly is a date):

set delexp=0; set addexp='\alnum{1,99}'; set addexp='\alnum=[\alnum\.]{1,99}'; create metamorph inverted index myindex on table(column1\column2,columndate)

(Adding columndate as a compound field speeds up the AND.)

[source1ben]

Does this rule also apply for ANDing statements?

thx.

[mark]

For "and" leave off the @0 . See http://www.thunderstone.com/site/texisman/node93.html

[source1ben]

How would I combine the ORing & ANDing. For example.

Include HOCKEY and one of the following STICK PUCK GOALIE "CENTER ICE" SKATE

...colA like '+HOCKEY STICK PUCK GOALIE "CENTER ICE" SKATE @0'

Is this right ???

[mark]

yes.